Set Operators
Domain 7 tests combining result sets vertically with UNION, UNION ALL, INTERSECT, and MINUS. The exam focuses on column compatibility, duplicate handling, and where ORDER BY is allowed.
Core rules
- Set operators combine two or more SELECT statements. Each SELECT must return the same number of columns, and corresponding columns must have compatible data types.
- Column names in the final result come from the first SELECT. ORDER BY applies to the entire combined result and must appear only at the end.
- UNION returns all distinct rows from both queries. UNION ALL returns all rows, including duplicates, and is usually faster.
- INTERSECT returns rows that appear in both result sets and removes duplicates. MINUS returns rows from the first result set that do not appear in the second and removes duplicates.
- Set operators treat NULLs as equal for duplicate elimination and set comparison, unlike ordinary WHERE equality.
- Parentheses can group SELECT statements when combining operators; otherwise follow Oracle precedence rules.
Worked example
SELECT department_id FROM employees MINUS SELECT department_id FROM departments WHERE department_id IS NOT NULL;
The first query returns all department_id values from employees, including duplicates. MINUS removes duplicates and retains only those values that do not appear in the second result set. The second query excludes NULL department_id values. The result is employee department IDs not present in departments.
Traps
Using UNION when duplicate retention is required changes results. Placing ORDER BY inside an individual SELECT in a compound query is invalid unless parentheses and inline-view rules are used. Column aliases from later SELECTs do not name the final result. Data type mismatch can cause implicit conversion or errors. MINUS is not symmetric; reversing the queries changes the answer.
Key points
- Same column count and compatible types are required.
- Final column names come from the first SELECT.
- UNION removes duplicates; UNION ALL keeps them.
- MINUS is directional.
- ORDER BY belongs at the end of the compound query.