Set operations
Combine or compare the rows of two queries — UNION, INTERSECT, EXCEPT — and why UNION ALL is usually the one you want.
The three operators
| Operator | Returns |
|---|---|
UNION | rows in either query, duplicates removed |
UNION ALL | rows in either query, duplicates kept |
INTERSECT | rows in both queries |
EXCEPT (MINUS in Oracle) | rows in the first query but not the second |
Each side must have the same number of columns with compatible types. Column names come from the first query.
UNION ALL vs UNION
SQL playground
Loading editor…
⌘/Ctrl + Enter
Now the deduplicating version:
SQL playground
Loading editor…
⌘/Ctrl + Enter
UNION sorts or hashes the combined result to remove duplicates — real cost
on large sets. If you know the two sides can't overlap (or you don't care about
dupes), use UNION ALL.
INTERSECT and EXCEPT
SQL playground
Loading editor…
⌘/Ctrl + Enter
SQL playground
Loading editor…
⌘/Ctrl + Enter
That last one is an anti-join — customers with no orders. NOT EXISTS is usually
clearer; see rows missing in another table.
Gotchas
ORDER BYgoes once, at the very end — it orders the whole combined result, not one side.INTERSECT/EXCEPTdedupe by default too;... ALLvariants (Postgres) keep multiplicity.- Type mismatches are resolved by the engine's coercion rules — an
intand atextcolumn stacked can produce surprising results or an error. - Not all engines have
INTERSECT/EXCEPT(older MySQL). Emulate withIN/NOT EXISTS.
- CTE (WITH) — recursive CTEs are built on
UNION ALL. - DISTINCT — the same dedup
UNIONdoes, on one query. - Rows missing in another table —
EXCEPTvsNOT EXISTS.