Combining Query Results As Sets
Set operators stack the results of two queries that have the same number and type of columns. They treat rows as set members rather than joining columns side by side.
The Operators
- UNION returns rows in either query and removes duplicates.
- UNION ALL returns everything including duplicates and is cheaper because it skips the dedupe.
- INTERSECT returns rows present in both queries.
- EXCEPT returns rows in the first query that are not in the second.
Rules And Costs
- Column counts and compatible types must line up positionally, not by name.
- The plain forms deduplicate, which requires a sort or hash, so they cost more than the ALL variants.
- ORDER BY applies to the combined result and appears once at the end.
When To Reach For Them
Set operators shine when you have two similarly shaped sources, such as finding ids that are in an allow list but not in a block list with EXCEPT, where a join would be clumsier.
Key idea
UNION INTERSECT and EXCEPT combine equally shaped result sets, dedupe by default, and UNION ALL skips the costly dedupe when duplicates are fine.