Set Operators
What the domain covers
Set operators combine result sets from two or more SELECT statements. This domain tests UNION, UNION ALL, INTERSECT, MINUS, column rules, and ordering.
Core rules
- Each SELECT must return the same number of columns, and corresponding columns must have compatible data types.
- Column names come from the first SELECT. Only the final ORDER BY can sort the combined result.
- UNION returns all distinct rows from both queries. UNION ALL returns all rows, including duplicates, and is usually faster.
- INTERSECT returns rows common to both queries, with duplicates removed.
- MINUS returns distinct rows from the first query that are not in the second. Order matters: A MINUS B is not B MINUS A.
- INTERSECT has higher precedence than UNION and MINUS; UNION and MINUS have equal precedence and evaluate left to right unless parentheses change the order.
- Parentheses can group queries; ORDER BY belongs after the last query.
Worked example
SELECT employee_id FROM employees MINUS SELECT employee_id FROM job_history;
Reasoning: both queries return one numeric column. MINUS removes duplicates and returns employee IDs present in employees but absent from job_history. These are employees who have never held a recorded previous job. Reversing the queries would return job-history employees not currently in employees, a different and possibly empty set.
Traps
UNION removes duplicates, so it can be slower than UNION ALL; do not use UNION when duplicates are meaningful. Do not assume column names come from the second query. ORDER BY inside an individual SELECT is invalid in a compound query. Mismatched column counts raise an error. INTERSECT and MINUS already remove duplicates.