Set operations
Stack the rows of two queries with UNION, INTERSECT, and EXCEPT.
- Combine query results with UNION (and UNION ALL)
- Know INTERSECT and EXCEPT
- Understand the column-compatibility rule
Joins combine tables side by side (more columns). Set operations stack results top to bottom (more rows) — they take two queries and combine their row sets.
UNION
UNION returns the rows from both queries, removing duplicates. The two
queries must produce the same number of columns, in compatible types:
Use UNION ALL to keep duplicates — it's also faster, since it skips the
de-duplication step. Reach for UNION ALL unless you specifically need uniques.
INTERSECT and EXCEPT
Two more, both also requiring matching columns:
- INTERSECT — rows present in both queries.
- EXCEPT — rows in the first query but not the second (a set difference).
SELECT product_id FROM order_items WHERE order_id = 1
EXCEPT
SELECT product_id FROM order_items WHERE order_id = 3;That's "products on order 1 but not on order 3."
The column-count/type rule is the whole catch: SELECT name and
SELECT id, name can't be UNIONed. Line the columns up first.
Where to go next
Last in the module: views — saving a query under a name to reuse it.