Cheatsheet
SQL Set Operations Cheatsheet
UNION, UNION ALL, INTERSECT, and EXCEPT side by side.
The four operators
| Operator | Keeps | Duplicates |
|---|---|---|
UNION ALL | All rows from both | Kept |
UNION | All rows from both | Removed |
INTERSECT | Rows in both | Removed |
EXCEPT | Rows in the first only | Removed |
They stack results vertically (more rows), unlike joins, which add columns.
The shape
One ORDER BY, at the very end
select state from customers
union all
select state from suppliers
order by stateCompatibility rules
- Every
SELECTreturns the same number of columns. - Matching columns have compatible types.
- Output column names come from the first
SELECT.
Common jobs
Stack two sources (keep everything)
select order_id, 'web' as channel from web_orders
union all
select order_id, 'store' from store_ordersValues shared by both (overlap)
select state from customers
intersect
select state from suppliersIn the first, not the second (missing list)
select customer_id from customers
except
select customer_id from ordersWatch out
Prefer UNION ALL unless you truly need to remove duplicates -UNION pays to sort and dedupe. Put a single ORDER BYafter the last SELECT; ordering the inputs won't control the combined order.