Subqueries & CTEs

Building complex queries in readable layers — and when to use each approach

Why

Real-world SQL questions require multiple steps — filter, aggregate, rank, then filter again. Subqueries and CTEs let you name intermediate results and build queries in layers. CTEs in particular transform deeply nested unreadable SQL into a step-by-step structure that reads almost like code comments, and are preferred over subquery nesting for readability.

Intuition

A subquery is a query embedded inside another query — either in FROM (derived table), WHERE (scalar or list subquery), or SELECT (correlated subquery). A CTE (Common Table Expression) is like giving a subquery a name at the top of the query. They're often equivalent in what they compute, but CTEs are almost always more readable.

Think of CTEs as named intermediate steps you build up, left to right, and then compose in the final SELECT — like variable assignment in a programming language.

Explanation

Subquery types

-- Scalar subquery — returns one value, usable anywhere SELECT name, salary, (SELECT AVG(salary) FROM employees) AS company_avg FROM employees; -- Subquery in FROM (derived table) — treated as a temporary table SELECT dept, avg_sal FROM ( SELECT department AS dept, AVG(salary) AS avg_sal FROM employees GROUP BY department ) dept_avgs WHERE avg_sal > 90000; -- Subquery in WHERE — filter using a list SELECT name FROM employees WHERE department_id IN ( SELECT id FROM departments WHERE location = 'NYC' ); -- Correlated subquery — references the outer query (runs per row) SELECT name, salary FROM employees e WHERE salary > ( SELECT AVG(salary) FROM employees WHERE department = e.department -- references outer row );

Correlated subqueries run once per outer row — they can be very slow on large tables. Rewrite them as a join or CTE with a pre-computed aggregate when performance matters.

CTEs with WITH

-- CTE: name intermediate results at the top, use below WITH dept_stats AS ( SELECT department, AVG(salary) AS avg_sal, COUNT(*) AS headcount FROM employees GROUP BY department ), top_depts AS ( SELECT department FROM dept_stats WHERE avg_sal > 100000 AND headcount >= 5 ) SELECT e.name, e.salary, e.department FROM employees e JOIN top_depts td ON e.department = td.department ORDER BY e.salary DESC; -- Recursive CTE — traverse hierarchies (org charts, graphs) WITH RECURSIVE org AS ( SELECT id, name, manager_id, 0 AS depth FROM employees WHERE manager_id IS NULL -- root (CEO) UNION ALL SELECT e.id, e.name, e.manager_id, o.depth + 1 FROM employees e JOIN org o ON e.manager_id = o.id -- join to parent ) SELECT * FROM org ORDER BY depth, name;

Recursive CTEs are the standard way to traverse hierarchical data — org charts, category trees, bill of materials. The anchor member (base case) runs once; the recursive member repeats until no new rows are added.

← Previous Next →