SQLite - UNIONS Clause

SQLite - The UNION and UNION ALL Operators

The UNION operator is used to combine the result sets of two or more SELECT statements into a single result set. It automatically removes duplicate rows from the final result.

For a UNION to work, the following conditions must be met:

• Each SELECT statement within the UNION must have the same number of columns.
• The columns must have similar (compatible) data types.
• The columns in each SELECT statement must also be in the same order.

The UNION Operator

Syntax

SELECT column1, column2 FROM table1
[WHERE condition]

UNION

SELECT column1, column2 FROM table2
[WHERE condition];

Example

Imagine a company stores data for full-time employees and contractors in separate tables. We want a single list of all people associated with the company.

Table 1: employees

ID          NAME        DEPARTMENT
----------  ----------  ----------
1           Alice       Engineering
2           Bob         Marketing
3           Charlie     Engineering

Table 2: contractors

ID          NAME        PROJECT
----------  ----------  ----------
1           David       'Website Redesign'
2           Charlie     'API Integration'
3           Eve         'Mobile App'

To get a unified list of names, we can use UNION. Notice that ‘Charlie’ exists in both tables, but UNION will only show him once.

SELECT name FROM employees
UNION
SELECT name FROM contractors
ORDER BY name;

This produces the following de-duplicated, sorted list:

name
----------
Alice
Bob
Charlie
David
Eve

The UNION ALL Operator

The UNION ALL operator works just like UNION, but it does not remove duplicate rows. It simply concatenates the result sets from the queries.

Performance Note

UNION ALL is significantly faster than UNION because it avoids the overhead of sorting and checking for duplicates. If you know your result sets have no duplicates, or if you intentionally want to include them, always prefer UNION ALL for better performance.

Syntax

SELECT column1, column2 FROM table1
[WHERE condition]

UNION ALL

SELECT column1, column2 FROM table2
[WHERE condition];

Example

Using the same employees and contractors tables, UNION ALL will include ‘Charlie’ twice.

SELECT name FROM employees
UNION ALL
SELECT name FROM contractors;

This produces the following result (the order is not guaranteed without ORDER BY):

name
----------
Alice
Bob
Charlie
David
Charlie
Eve

UNION with Different Column Structures

You can use UNION on tables with different structures as long as you select the same number of columns with compatible types. You can use literal values (like a string) to fill in for missing columns.

-- Get a list of all people and their roles
SELECT name, 'Employee' AS role, department FROM employees

UNION ALL

SELECT name, 'Contractor' AS role, project FROM contractors

ORDER BY name;

This query creates a unified list, creating a role column to distinguish between the two types of personnel. The department and project columns are combined into one, which is acceptable because they both contain text.

name       role        department
---------  ----------  -----------------
Alice      Employee    Engineering
Bob        Employee    Marketing
Charlie    Contractor  API Integration
Charlie    Employee    Engineering
David      Contractor  Website Redesign
Eve        Contractor  Mobile App