Combines result sets from multiple queries.
<query1>
UNION [ALL] | INTERSECT [ALL] | EXCEPT [ALL]
<query2>## Overview Set operations combine the result sets of two or more SELECT statements into a single result. UNION merges rows from both queries. INTERSECT returns only rows present in both queries. EXCEPT returns rows from the first query that do not appear in the second. Each operation has an ALL variant that preserves duplicates and a default (non-ALL) variant that eliminates them. ## Execution Order Set operations are evaluated after each individual SELECT statement has been fully processed (including its own WHERE, GROUP BY, HAVING, and SELECT clauses). The set operation then combines the result sets. An ORDER BY or LIMIT that appears after the final query in the chain applies to the combined result. To sort or limit an individual branch, wrap it in a subquery. ## Behavior - **UNION**: Concatenates the result sets and removes duplicate rows. Duplicate detection compares entire rows, treating two NULLs in the same column position as equal. - **UNION ALL**: Concatenates the result sets without removing duplicates. This is significantly faster than UNION because it avoids the deduplication hash or sort. - **INTERSECT**: Returns rows that appear in both result sets. Without ALL, duplicates are removed from the output. With ALL, the number of duplicate rows in the output equals the minimum of the duplicate counts from each side. - **EXCEPT**: Returns rows from the left query that do not appear in the right query. Without ALL, duplicates are removed. With ALL, each occurrence in the right side cancels one occurrence from the left side. - **Column compatibility**: All branches of a set operation must produce the same number of columns. Column names are taken from the first query. Data types are coerced to a common type using implicit casting rules; if no common type exists, an error is raised. - **Precedence**: INTERSECT has higher precedence than UNION and EXCEPT, following the SQL standard. `A UNION B INTERSECT C` is evaluated as `A UNION (B INTERSECT C)`. Use parentheses to override precedence. - **NULL handling**: For deduplication in UNION, INTERSECT, and EXCEPT (non-ALL), two NULL values in the same column position are considered equal. This differs from general comparison semantics where NULL = NULL is UNKNOWN. - **Optimization**: The optimizer pushes filters from the outer query down into individual set operation branches when safe, enabling partition pruning on each branch independently. However, EXCEPT and EXCEPT ALL block pushdown of certain predicates because adding a filter could incorrectly include rows that should be subtracted. ## Compatibility UNION, INTERSECT, and EXCEPT conform to the SQL:1992 standard. The ALL variants and INTERSECT/EXCEPT precedence rules conform to SQL:2003. Parenthesized set operations for explicit precedence control are fully supported.
-- UNION: combine and deduplicate employee and contractor names
SELECT name, email FROM employees
UNION
SELECT name, email FROM contractors;-- UNION ALL: combine without deduplication (faster)
SELECT id, amount, 'domestic' AS source FROM domestic_orders
UNION ALL
SELECT id, amount, 'international' AS source FROM international_orders;-- INTERSECT: rows present in both result sets
SELECT customer_id FROM online_orders
INTERSECT
SELECT customer_id FROM in_store_orders;-- EXCEPT: rows in the first set but not in the second
SELECT user_id FROM all_users
EXCEPT
SELECT user_id FROM blocked_users;-- Chained set operations with ORDER BY on the combined result
SELECT name, 'employee' AS type FROM employees
UNION ALL
SELECT name, 'contractor' AS type FROM contractors
UNION ALL
SELECT name, 'intern' AS type FROM interns
ORDER BY name;-- EXCEPT ALL: preserves duplicate counts
SELECT product_id FROM warehouse_stock
EXCEPT ALL
SELECT product_id FROM shipped_items;