Using Subqueries and Set Operators
Using Subqueries and Set Operators
This domain tests nested queries and combining result sets. You need to distinguish single-row from multi-row subqueries, understand correlated execution, and apply set operators with compatible column lists.
Core rules
- A single-row subquery returns at most one row and uses =, <, >, <=, >=, or <>. A multi-row subquery uses IN, ANY, ALL, or EXISTS.
- A correlated subquery references a column from the outer query and is evaluated per outer row. A noncorrelated subquery can run independently.
- A scalar subquery returns one column and one row and can appear in SELECT, WHERE, or HAVING.
- EXISTS tests whether the subquery returns any row; NOT EXISTS is null-safe, unlike NOT IN when the subquery returns null.
- Set operators are UNION, UNION ALL, INTERSECT, and MINUS. Queries must have the same number of columns with compatible data types. Column names come from the first query.
- UNION removes duplicates and sorts; UNION ALL keeps duplicates. INTERSECT returns common rows; MINUS returns rows in the first query not in the second. ORDER BY applies to the final result and appears once at the end.
Worked example
SELECT employee_id, last_name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees) ORDER BY salary DESC;
The inner query returns one average salary. The outer query compares each employee salary to that scalar. Because the subquery is noncorrelated, it can be evaluated once. If it returned multiple rows, the > comparison would raise an error.
Traps
Greater than ANY means greater than the minimum; greater than ALL means greater than the maximum. NOT IN returns no rows if the subquery contains a null. A correlated subquery without a proper outer alias can reference the wrong table. ORDER BY inside an inline view or set-operator branch is restricted unless paired with row limiting. Set operators compare nulls as equal for duplicate elimination, but UNION ALL does not eliminate duplicates.