P07 · SQL & Querying
SQL UNION vs UNION ALL — and set-op traps
How SQL set operations combine result sets — and why UNION can silently drop a row that UNION ALL keeps, quietly lowering your total.
Start with the basics
Set operations combine the results of two queries into one. UNION and UNION ALL stack them on top of each other; EXCEPT and INTERSECT compare them.
UNION returns the combined rows with duplicates removed. UNION ALL returns everything, duplicates included — and it skips the extra dedup work, so it is also faster.
The two look interchangeable in a quick read, but which one you pick changes both the row count and the cost — and the difference is silent.
One that works
| list | |
|---|---|
| promo | a@x |
| promo | b@x |
| newsletter | b@x |
| newsletter | c@x |
SELECT email FROM promo
UNION
SELECT email FROM newsletter
ORDER BY email;| a@x |
| b@x |
| c@x |
UNION stacks both lists and removes the duplicate b@x — three distinct addresses.
Set operations stack two result sets into one — UNION removes duplicates, UNION ALL keeps them.
When to use it
- Reach for UNION or UNION ALL when you want to stack two similar result sets into one list.
- Use EXCEPT or INTERSECT to compare two sets — what is in one but not the other, or in both.
- If you are combining lists by hand in your app, a set operation usually does it in one query.
That one word — removes — is where a UNION quietly costs you a row. When the two sets overlap, the duplicate you were counting silently merges into one. Here is the day that bites.
See it happen
The list that loses a row — UNION vs UNION ALL
You are sending one email per address across two lists. One address is on both lists. Watch what UNION does with that overlap.
| a@x |
| b@x |
| b@x |
| c@x |
UNION — merges the duplicate
SELECT email FROM promo
UNION
SELECT email FROM newsletter
ORDER BY email;| a@x |
| b@x |
| c@x |
UNION ALL — keeps both
SELECT email FROM promo
UNION ALL
SELECT email FROM newsletter
ORDER BY email;| a@x |
| b@x |
| b@x |
| c@x |
The “total emails to send” count reads 3, not 4 — UNION silently merged the address that was on both lists.
Walk the full example in the unit →The rest of the family
More ways a set operation misleads
EXCEPT and INTERSECT compare whole rows treating two missing values as equal — the opposite of a normal equality test, which is exactly why EXCEPT is a safe anti-join for columns that can be blank.
EXCEPT keeps rows in the first set that are not in the second; INTERSECT keeps rows in both — the set versions of “not in” and “in”.
Set operations match columns by their position, not their name, so swapping two columns produces plausible-looking garbage.
Self-check
How to spot it in your own SQL
- A combined total from two lists is lower than the two lists added together.
- You stacked two queries and a duplicate silently disappeared — or you needed it kept.
- You are combining set queries and the columns look shifted.
- You are trying to find rows in one list but not another.
What people miss
Set operations look interchangeable until a UNION silently swallows a row you were counting, or a column swap produces a plausible-but-wrong list. The engine never complains — the total just quietly reads low.
Go deeper
Ask Colearn
Instant answers, grounded in the same verified material the diagnostic grades against.
Why is my UNION total lower than expected?
UNION removes duplicates, so any row that appears in both queries is merged into one — which quietly lowers your count when the two sets overlap.
Use UNION ALL to keep every row (and it is also cheaper, since it skips the dedup step).
from the unit →How do I find rows in one table but not another?
Use EXCEPT: it keeps the rows of the first query that are not in the second — a set-based anti-join. INTERSECT keeps the rows in both.
EXCEPT is also safe when the columns can be blank, because set operations treat two missing values as equal.
from the unit →Why did my UNION mix up the columns?
Set operations line up columns by position, not by name, so if the two SELECTs list columns in a different order the values land in the wrong columns.
Make both queries list the columns in the same order (and compatible types).
from the unit →Are set operations safe with NULLs?
Yes — EXCEPT and INTERSECT treat two missing values as equal when they compare whole rows, the opposite of a normal equality test.
That is why EXCEPT is the safe way to subtract one list from another when the key can be blank.
from the unit →That’s the free taste