UNION, UNION ALL, INTERSECT and EXCEPT
Set operators stack the results of two queries. UNION ALL keeps every row, and UNION removes duplicates. INTERSECT keeps rows found in both, and EXCEPT keeps rows in the first but not the second.
Why it matters
Sometimes the rows you need come from two places: every city mentioned by departments or customers; cities that have both a department and a customer. Joins put tables side by side; set operators put results on top of each other.
The idea
Write two SELECTs with the same number of columns, of matching types, and put a set operator between them. The column names come from the first SELECT.
UNION ALL simply stacks the rows — fast, and keeps duplicates. UNION stacks them and removes duplicate rows — which costs a sort or hash. INTERSECT keeps rows in both results; EXCEPT keeps rows of the first that are not in the second.
A single ORDER BY at the very end sorts the combined result.
Two class lists. UNION ALL staples them together. UNION staples them and crosses out anyone listed twice. INTERSECT is pupils on both lists. EXCEPT is pupils on the first list but not on the second.
Pupils are matched by name. Set operators match whole rows, column by column — so adding a column that differs (like a source label) stops rows from counting as duplicates.
Syntax
SELECT col_a, col_b FROM table_one
UNION ALL
SELECT col_x, col_y FROM table_two
ORDER BY col_a;How it works
- Both SELECTs return one column: department locations and customer cities.
- UNION stacks them (10 rows) and removes duplicate rows, leaving 5 different cities, including NULL.
- UNION ALL keeps all of them; a source column shows where each row came from.
- INTERSECT keeps only cities in both lists: Leeds and London.
- EXCEPT keeps department cities with no customer: Manchester.
Key points
- Both queries need the same number of columns, in the same order, with compatible types.
- UNION ALL keeps duplicates and is faster; UNION removes them.
- Use UNION ALL unless you really need duplicates removed.
- INTERSECT and EXCEPT also remove duplicates from their result.
- One ORDER BY, at the end, sorts the combined result; column names come from the first query.
Common mistakes
- Using UNION when the two sets can't overlap — paying for duplicate removal you don't need.
- Putting ORDER BY on the first query instead of at the end.
- Lining columns up in a different order in the second query: they are matched by position, not by name.
Not true: UNION matches the columns of its two queries by name.
In fact: Columns are matched by position, not by name. If the second query lists them in another order, the values land in the wrong columns.
In the interview
Set operators combine two result sets vertically, matching columns by position. UNION ALL appends everything; UNION also removes duplicate rows, which costs a sort or hash, so I default to UNION ALL unless duplicates must go. INTERSECT returns rows present in both results and EXCEPT returns rows in the first but not the second — a neat alternative to an anti-join when comparing id lists.
Part of Unit 4: Subqueries, CTEs and set operations in the free SQL course.