Set Operations
UNION combines results of two queries, removing duplicates. UNION ALL combines without removing duplicates (faster). INTERSECT returns rows in both result sets. EXCEPT returns rows in the first set not in the second.
UNION is a merge of two playlists removing duplicate songs (expensive dedup). UNION ALL is just combining both playlists. INTERSECT is songs on both playlists. EXCEPT is songs on the first playlist but not the second.
Rules: both queries must have the same number of columns with compatible types. Column names come from the first query. UNION is equivalent to UNION ALL + SELECT DISTINCT — it performs a sort or hash to deduplicate, which is expensive. Use UNION ALL whenever duplicates are acceptable or impossible. INTERSECT and EXCEPT are equivalent to semi-join and anti-join respectively, and the planner may convert them. ORDER BY applies to the final combined result, not individual parts.
UNION ALL is almost always preferred over UNION for performance — UNION adds an O(n log n) deduplication step. Use UNION only when you genuinely need distinct rows across multiple queries. EXCEPT is null-safe (unlike NOT IN) — two NULLs are treated as equal for the purpose of set difference. For finding symmetric differences (rows in either set but not both), combine two EXCEPTs with UNION ALL. INTERSECT can be faster than EXISTS for some query shapes because the planner has a dedicated INTERSECT path.
I use UNION ALL by default — UNION adds expensive deduplication. EXCEPT is my preference over NOT IN for anti-joins because it handles NULLs correctly. INTERSECT for rows present in both result sets. The columns must match in count and compatible types across all parts of the set operation.
Using UNION instead of UNION ALL when results can't have duplicates anyway. The extra deduplication sort/hash costs CPU and memory with zero benefit.