~/learn/SQL · Joins
UNION, INTERSECT and EXCEPT
Stack and compare whole result sets.
why this matters
Sometimes you need to stack two lists or compare them — customers who bought this year but not last year, for example.
| idINTEGER | nameTEXT | countryTEXT | signup_dateTEXT |
|---|---|---|---|
| 1 | Ada Lovelace | UK | 2023-01-14 |
| 2 | Grace Hopper | USA | 2023-02-02 |
| 3 | Alan Turing | UK | 2023-02-19 |
| 4 | Katherine Johnson | USA | 2023-03-05 |
showing 4 of 8 rows · 4 columns
UNION ALL stacks two results on top of each other; both sides need the same number of columns in the same order. UNION does the same but removes duplicates, which costs a sort.
Try it: Restrict the product side to the Audio category.
INTERSECT keeps rows present in both results and EXCEPT keeps rows present in the first but not the second — a quick way to diff two populations.
Note
Prefer UNION ALL unless you actually need de-duplication — it is cheaper, and it keeps legitimately repeated rows.
key takeaways
- UNION stacks results and removes duplicates.
- UNION ALL keeps duplicates (and is faster).
- EXCEPT returns rows in the first result but not the second.
check your understanding
Both queries must have…
free practice
Build confidence with a question you can solve without Pro.