1Z0-149: Using Set Operators

What this domain tests

Set operators combine result sets vertically. The exam tests UNION, UNION ALL, INTERSECT, MINUS, and the rules for compatible queries.

Core rules

• 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. MINUS returns rows in the first query that are not in the second.
• Each query 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 query.
• ORDER BY appears only once, at the end, and applies to the combined result.
• Set operators have equal precedence and are evaluated left to right; use parentheses to force a different order.
• For duplicate elimination and set comparison, NULLs are treated as equal.

Worked example

SELECT department_id FROM employees WHERE salary > 10000 MINUS SELECT department_id FROM departments WHERE location_id = 1700 ORDER BY department_id;

MINUS first builds the set of departments that have a high-salary employee, then removes departments located in 1700. The final ORDER BY sorts the combined result. If the first query returned duplicate department IDs, MINUS would remove duplicates because set operators return distinct rows.

Traps

• Putting ORDER BY inside either branch.
• Using UNION when duplicates are meaningful or performance matters; UNION ALL preserves them.
• Mismatching column counts or incompatible types, such as a date against a number without conversion.
• Expecting the second query's column names to appear in the output.
• Assuming MINUS is symmetrical; it is not.