Subqueries, CTEs and set operations

Why it matters

Many questions are naturally nested: "employees earning more than their department's average", "products never ordered", "the whole reporting chain under a manager". Subqueries and common table expressions express that nesting directly, set operations combine whole result sets, and recursive CTEs handle hierarchies that plain SQL cannot. They are standard interview material and the main tool for making complex SQL readable.

The idea

A subquery is a SELECT used inside another statement. Where it appears decides what shape it must return.

Correlated versus uncorrelated

An uncorrelated subquery does not refer to the outer query; it can be computed once. A correlated subquery refers to columns of the outer row and logically runs once per outer row:

-- employees earning more than the average of their own department (correlated)
SELECT e.name, e.salary
FROM employee e
WHERE e.salary > (SELECT AVG(e2.salary) FROM employee e2 WHERE e2.dept_id = e.dept_id);

Modern optimizers usually decorrelate such queries into joins with a grouped derived table, so correlated does not automatically mean slow; check the plan. The same question can also be written with a window function, covered next lesson.

IN, EXISTS, ANY and ALL

Common table expressions

A CTE names a subquery with WITH so the main query reads top to bottom, and the same intermediate result can be referenced more than once:

WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_salary
  FROM employee
  GROUP BY dept_id
)
SELECT e.name, e.salary, d.avg_salary
FROM employee e
JOIN dept_avg d ON d.dept_id = e.dept_id
WHERE e.salary > d.avg_salary;

Most databases inline CTEs like views and optimise across them; some can materialise a CTE once (PostgreSQL lets you choose with MATERIALIZED). CTEs are primarily a readability tool.

Recursive CTEs

A recursive CTE has an anchor part and a recursive part joined with UNION ALL; it repeats the recursive part on the rows produced by the previous step until no new rows appear. It is how SQL walks hierarchies and graphs.

WITH RECURSIVE chain AS (
  SELECT emp_id, name, manager_id, 0 AS depth FROM employee WHERE emp_id = 7
  UNION ALL
  SELECT e.emp_id, e.name, e.manager_id, c.depth + 1
  FROM employee e JOIN chain c ON e.manager_id = c.emp_id
)
SELECT * FROM chain;

Guard against cycles in graph data with a depth limit or a visited-path column.

Set operations

UNION, INTERSECT and EXCEPT combine two result sets with the same number of compatible columns. UNION, INTERSECT and EXCEPT remove duplicates; UNION ALL keeps them and is faster because it skips the de-duplication sort or hash, so use it whenever duplicates are impossible or wanted. Column names come from the first query, and ORDER BY applies to the combined result.

Trade-offs

Subqueries often read closest to the English question; joins and window functions often run faster or make the plan clearer. CTEs improve readability at no cost in most engines, but materialised CTEs can block optimisations such as pushing filters inside. Recursive CTEs make hierarchies possible in plain SQL but can be expensive on deep or cyclic graphs, where a dedicated structure (closure tables, a graph database) may be better.

In an interview

Pick the clearest form and mention alternatives: "Employees above their department average: a correlated subquery reads naturally; a CTE with the department averages joined back is clearer and usually the same plan; AVG as a window function avoids the join altogether." For hierarchies, write the recursive CTE with an anchor and a recursive step and mention cycle protection.