SQL / JOINS
UNION versus UNION ALL and the duplicate question
Choose between UNION and UNION ALL with confidence: know which rows collapse, why the dedup costs work, and when it silently breaks a total.
What you will learn
- Read UNION as UNION ALL followed by a whole-row DISTINCT over the combined result
- Default to UNION ALL when branches are disjoint and skip a sort you do not need
- Remember UNION also drops duplicates that existed inside a single branch
- Detect wrong SUM and COUNT results caused by UNION collapsing real repeated rows
Understanding UNION versus UNION ALL and the duplicate question
Every join in this section makes rows wider by matching them side by side. UNION and UNION ALL do the opposite: they stack two result sets on top of each other, so the branches must agree on column count and compatible types, matched by position rather than by name. UNION ALL is the primitive operation, emitting every row of the first branch and then every row of the second. UNION is that same concatenation with one extra step bolted on afterwards: a DISTINCT pass over the whole combined result.
That DISTINCT pass compares entire rows, all selected columns at once, and it does not care which branch a row came from. A duplicate that already sat inside one table is therefore removed too, while two rows that agree on the id you think of as the key both survive if any other column differs. The comparison also treats two NULLs as equal, which is the opposite of how NULL behaves in a join condition or a WHERE clause. And because the engine cannot know in advance that your branches are disjoint, it pays for a sort or hash over the combined output whether or not anything is actually removed.
So the choice is not stylistic. Ask what a repeated row means in your data: two rows reading (day 3, amount 50) are usually two real sales, and collapsing them understates every total computed afterwards. When the branches are genuinely disjoint, such as separate months, archived versus live rows, or per-region tables, UNION ALL is both correct and cheaper, since the dedup can only remove rows you wanted. Reach for UNION when you want a set of distinct values, and when you need distinctness on only some columns, keep UNION ALL and do the collapsing yourself with GROUP BY.
placeholder
CREATE TABLE newsletter (email TEXT);
CREATE TABLE workshop (email TEXT);
INSERT INTO newsletter VALUES ('ana@mail.test'), ('bo@mail.test'), ('ana@mail.test');
INSERT INTO workshop VALUES ('bo@mail.test'), ('cy@mail.test');
-- raw concatenation: 3 rows then 2 rows
SELECT email FROM newsletter
UNION ALL
SELECT email FROM workshop
ORDER BY email;
-- the same concatenation, then one whole-row DISTINCT pass
SELECT email FROM newsletter
UNION
SELECT email FROM workshop
ORDER BY email;UNION is UNION ALL plus a whole-row DISTINCT, so the only question that matters is whether a repeated row is a fact you need to count or an artifact of overlapping sources.
Worked examples
Dedup compares the whole row
Shows that UNION collapses rows only when every selected column matches, not when a single identifying column matches.
CREATE TABLE store_a (sku TEXT, qty INTEGER);
CREATE TABLE store_b (sku TEXT, qty INTEGER);
INSERT INTO store_a VALUES ('K-100', 4), ('K-200', 7);
INSERT INTO store_b VALUES ('K-100', 4), ('K-200', 9);
SELECT sku, qty FROM store_a
UNION
SELECT sku, qty FROM store_b
ORDER BY sku, qty;Example explained
Line 1Both tables contain ('K-100', 4), identical in every selected column, so UNION keeps one copy.
Line 2('K-200', 7) and ('K-200', 9) differ in qty, so they are different rows and both survive; UNION does not dedup by sku.
Line 3Four input rows become three, and that gap of one is exactly how much true overlap the two branches had.
Line 4ORDER BY sku, qty applies to the combined result; without it the three rows may come back in any order.
How UNION corrupts a total
Demonstrates that legitimately repeated rows disappear under UNION, producing a wrong sum with no error.
CREATE TABLE jan_sales (sale_day INTEGER, amount INTEGER);
CREATE TABLE feb_sales (sale_day INTEGER, amount INTEGER);
INSERT INTO jan_sales VALUES (3, 50), (3, 50), (9, 20);
INSERT INTO feb_sales VALUES (3, 50);
SELECT 'UNION' AS variant, SUM(amount) AS total
FROM (SELECT sale_day, amount FROM jan_sales
UNION
SELECT sale_day, amount FROM feb_sales) u
UNION ALL
SELECT 'UNION ALL', SUM(amount)
FROM (SELECT sale_day, amount FROM jan_sales
UNION ALL
SELECT sale_day, amount FROM feb_sales) a
ORDER BY variant;Example explained
Line 1jan_sales holds (3, 50) twice: two separate sales of the same amount on the same day, which is ordinary data.
Line 2The UNION subquery folds those two rows and the identical February row into a single row, so SUM sees 50 + 20 = 70.
Line 3The UNION ALL subquery keeps all four rows and reports the true 170; nothing errors out in either case, which is what makes the bug hard to notice.
Line 4ORDER BY variant makes the two-row report deterministic, and 'UNION' sorts before 'UNION ALL' because it is a prefix of it.
Columns pair by position, names come from the first branch
Shows that differently named columns combine fine, and that the trailing ORDER BY can only use the first branch's names.
CREATE TABLE staff (staff_name TEXT, hired INTEGER);
CREATE TABLE contractors (full_name TEXT, started INTEGER);
INSERT INTO staff VALUES ('Ada', 2019), ('Lin', 2021);
INSERT INTO contractors VALUES ('Ada', 2019), ('Raj', 2023);
SELECT staff_name, hired FROM staff
UNION
SELECT full_name, started FROM contractors
ORDER BY staff_name;Example explained
Line 1staff_name pairs with full_name and hired pairs with started purely because of their position in each SELECT list.
Line 2The result columns are named after the first branch only, so ORDER BY staff_name works while ORDER BY full_name is an error.
Line 3('Ada', 2019) exists in both tables and collapses to one row; the source column names play no part in that comparison.
Line 4Swapping the second branch to SELECT started, full_name would raise a type mismatch here, but with two same-typed columns the same mistake is silent.
Important notes
Chained operators apply left to right, so A UNION ALL B UNION C dedups all three branches, including duplicates inside A; parenthesize if you want the ALL to survive.
UNION ALL is usually cheaper, but not always dramatically so: when both inputs already arrive sorted by an index the engine can dedup with a cheap merge, so measure before rewriting a working query.
Common mistakes
Using UNION as the default way to stack two tables and then aggregating: two genuine rows with identical values collapse into one, so SUM and COUNT come back low and nothing warns you.
Expecting UNION to dedup on an id while also selecting a timestamp or a source label; those columns make every row unique, the duplicates reappear, and the real fix is GROUP BY rather than a different set operator.
Writing ORDER BY or LIMIT after the first branch and expecting it to apply to that branch only; most engines reject it without parentheses, and a trailing clause sorts or truncates the combined result instead.
Try it yourself
Change, predict, then run
In a browser SQL editor, create two three-row tables of tag names where one tag appears in both tables and one table repeats a tag internally, then run the same SELECT with UNION ALL and with UNION and account for every row that vanished.
Open the SQL workspaceCheck your understanding
Branch A returns 300 rows, branch B returns 200 rows, and all 500 rows are distinct from one another. What is the practical difference between combining them with UNION and with UNION ALL?
- Both return 500 rows, but UNION still sorts or hashes the combined output to prove no duplicates exist.
- Both return 500 rows and the two queries do identical work, since there is nothing to remove.
- UNION returns 500 rows and UNION ALL returns 1000, because ALL reads each branch twice.
- UNION returns fewer than 500 rows, because it compares the key column of A against the key column of B.
Show answer
The engine has no way to know the branches are disjoint, so UNION always performs duplicate elimination: a sort or hash over all 500 rows that also stops it returning anything until the pass is under way, even though it removes nothing. Option 1 is tempting because the two result sets are identical, but identical output does not mean identical work; option 3 confuses UNION's whole-row comparison with matching on a key, which is join behaviour, not set behaviour.