SQLite UNION vs UNION ALL Explained with Examples
For someone who writes SELECT statements in SQLite and needs the rows of two of them in one result set; duplicate handling and the compound rules are explained where they appear.
On this page
Two tables hold the same kind of row, and the report wants both: staff and contractors on one list, this month's invoices and last month's, the archive table and the live one. UNION stacks the two result sets into one and drops the rows that appear twice. UNION ALL stacks them and keeps everything, including the duplicates, which is why it does less work.
The examples use two tables with the same three columns and one person who appears in both:
CREATE TABLE Employees (
id INTEGER PRIMARY KEY,
name TEXT,
department TEXT
);
INSERT INTO Employees (name, department) VALUES
('John Doe', 'Sales'),
('Jane Doe', 'Marketing');
CREATE TABLE Contractors (
id INTEGER PRIMARY KEY,
name TEXT,
department TEXT
);
INSERT INTO Contractors (name, department) VALUES
('John Doe', 'Sales'),
('Mark Smith', 'Sales'),
('Jane Smith', 'Marketing');
What UNION returns and what UNION ALL returns
Both statements below are single SQL statements, so they run in the sqlite3 shell as they stand, after .open sampleDB.db and the CREATE TABLE block above. If you have no file yet, our articles on creating a SQLite database and creating a table cover both steps.
UNION removes duplicate rows from the final result set, so the John Doe who works in Sales on both payrolls appears once:
SELECT name, department FROM Employees
UNION
SELECT name, department FROM Contractors
ORDER BY name;
| name | department |
|---|---|
| Jane Doe | Marketing |
| Jane Smith | Marketing |
| John Doe | Sales |
| Mark Smith | Sales |
UNION ALL returns every row from the SELECT on its left and every row from the SELECT on its right, with no comparison in between. The same query returns five rows instead of four:
SELECT name, department FROM Employees
UNION ALL
SELECT name, department FROM Contractors
ORDER BY name;
| name | department |
|---|---|
| Jane Doe | Marketing |
| Jane Smith | Marketing |
| John Doe | Sales |
| John Doe | Sales |
| Mark Smith | Sales |
Duplicate here means the whole row, not one column. Had the contractor John Doe been booked to Marketing, the two rows would differ in the department and UNION would have kept both.
Why UNION costs more than UNION ALL
Removing duplicates is work SQLite has to do somewhere. Its own EXPLAIN QUERY PLAN examples show where: a compound query built with UNION carries a UNION USING TEMP B-TREE node, a temporary b-tree SQLite fills in order to recognize the rows it has already emitted. UNION ALL needs no such node, because it never compares one row against another.
So write UNION ALL whenever duplicates cannot occur. That is the common case for tables that partition the data between them: an archive table beside a live one, or one month per table. Write UNION when a row genuinely can appear in both inputs and you want it once. Writing UNION out of habit on inputs that never overlap pays for a sort that finds nothing.
The rules a compound SELECT must follow
A compound SELECT is two or more SELECT statements joined by UNION, UNION ALL, INTERSECT or EXCEPT, and each of them is called a term. Four rules from the SELECT documentation decide whether yours parses:
- All the terms must return the same number of result columns.
- A term may not carry its own ORDER BY or LIMIT clause. Both go at the end of the whole compound, and both then apply to the whole compound, as the
ORDER BY nameabove does. - A compound whose right-most term is a VALUES clause takes neither ORDER BY nor LIMIT.
- Three or more terms group from left to right, so
A UNION B UNION Cis evaluated as(A UNION B) UNION C.
Two more rules decide what counts as a duplicate. NULL values are considered equal to other NULL values and distinct from every non-NULL value, which is the opposite of how NULL behaves in a WHERE clause, and no affinity transformation is applied to any value while rows are compared. A text '1' in one term and an integer 1 in the other are therefore two rows, not one.
The last limit is one you are unlikely to reach. The number of terms in a compound SELECT is capped by SQLITE_MAX_COMPOUND_SELECT, which defaults to 500.
UNION and UNION ALL side by side
| UNION | UNION ALL | |
|---|---|---|
| Duplicate rows | Removed | Kept |
| Extra work | A temporary b-tree to compare rows | None |
| Rows returned by the example above | 4 | 5 |
UNION in the DbSchema SQL Editor
Setting up two tables and then querying across them takes two different run actions in DbSchema. Open the SQL Editor from the Editors menu or the toolbar and paste the block that creates both tables. Press Run Script, which executes the entire editor content, and DbSchema runs the statements in order. Then select the UNION query alone and press Execute Query, which runs the selected text and shows the four rows as a result table.
Run Script and Execute Query both send their statements to the connected SQLite database, so the CREATE TABLE statements above create real tables in the file. The DbSchema model file holds the diagram and the editors themselves, and a Refresh Model from Database brings the two new tables into it. The SQL Editor and the diagrams are in the free Community Edition.
The choice between the two operators is really a question about your data: can the same row come out of both inputs, and do you want it twice? Answer that first, and UNION ALL turns out to be the right default more often than habit suggests. DbSchema draws both tables on one diagram and runs the compound query against the file itself, so the columns each term selects are in front of you as you write them. Download it at https://dbschema.com/download.html, connect to your SQLite database, and start with the UNION ALL above; the diagram and the SQL Editor are in the free Community Edition.
