Subqueries and CTEs

Subqueries in WHERE#

A subquery is a SELECT nested inside another query. Placed inside WHERE, it computes a value (or a set of values) the outer query filters against:

-- Products priced above the overall average price
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

The inner query runs once, produces a single value (the average price), and the outer query compares each row against it. A subquery returning multiple rows pairs with IN:

-- Users who have placed at least one order
SELECT name
FROM users
WHERE id IN (SELECT user_id FROM orders);

Subqueries in FROM (derived tables)#

A subquery can also stand in for a table in FROM — the result of the inner query becomes a temporary, unnamed table the outer query can select from and join against. It must be given an alias:

SELECT category, avg_price
FROM (
    SELECT category, AVG(price) AS avg_price
    FROM products
    GROUP BY category
) AS category_averages
WHERE avg_price > 30;

This is useful whenever a query needs to filter or join on the result of an aggregation, rather than on raw rows — HAVING alone can't express "join this summary to another table."

Correlated subqueries#

A correlated subquery references a column from the outer query, so it can't run once in isolation — conceptually, it re-runs once per outer row:

-- Each product's price compared only against other products in its own category
SELECT p1.name, p1.category, p1.price
FROM products p1
WHERE p1.price > (
    SELECT AVG(p2.price)
    FROM products p2
    WHERE p2.category = p1.category   -- references the outer row
);

Compare that to the plain (non-correlated) subquery earlier, which computed one global average regardless of which outer row was being evaluated. Correlated subqueries are powerful but can be slow on large tables since a naive execution plan implies one inner query per outer row — modern query planners often rewrite them into a join internally, but it's not guaranteed, so it's worth checking EXPLAIN on a correlated subquery against a large table (see the MySQL and PostgreSQL indexing pages in this track for EXPLAIN).

EXISTS is the most common correlated pattern — it only cares whether the inner query returns any row at all, not what value it returns:

-- Users who have placed at least one shipped order
SELECT name
FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id AND o.status = 'shipped'
);

EXISTS typically outperforms an equivalent IN subquery once the inner set is large, because the database can stop as soon as it finds one match instead of building the full inner result set first.

Common Table Expressions (CTEs)#

A CTE, written with WITH ... AS (...), names a subquery up front so the main query can read like ordinary prose instead of nesting nested queries several levels deep. It's a readability tool first — most CTEs could be written as an equivalent subquery, but naming each step makes the intent explicit:

WITH order_totals AS (
    SELECT user_id, SUM(quantity) AS total_units
    FROM orders
    GROUP BY user_id
)
SELECT u.name, ot.total_units
FROM users u
INNER JOIN order_totals ot ON u.id = ot.user_id
WHERE ot.total_units > 1;

Multiple CTEs can be chained, each one able to reference the ones defined before it:

WITH order_totals AS (
    SELECT user_id, SUM(quantity) AS total_units
    FROM orders
    GROUP BY user_id
),
big_spenders AS (
    SELECT user_id FROM order_totals WHERE total_units > 1
)
SELECT u.name
FROM users u
INNER JOIN big_spenders b ON u.id = b.user_id;

Recursive CTEs#

A recursive CTE repeatedly re-applies itself to walk hierarchical or graph-like data — the classic use case is an organization chart or category tree stored as a self-referencing table (see the self-join example in the joins page). It has an "anchor" (base case) and a "recursive" branch, unioned together:

WITH RECURSIVE org_chart AS (
    -- Anchor: the top-level employee (no manager)
    SELECT id, name, manager_id, 1 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive step: join back to org_chart itself, one level down each time
    SELECT e.id, e.name, e.manager_id, oc.depth + 1
    FROM employees e
    INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY depth;

The recursive branch keeps joining newly found rows back against org_chart until a pass produces no new rows, at which point it stops automatically.

Subquery vs join#

A join and an equivalent subquery are often interchangeable, and a good query planner frequently produces the same execution plan for both. As a rule of thumb: reach for a join when you need columns from both tables in the result; reach for EXISTS/IN when you only need to filter one table based on whether a related row exists, without pulling any of that related row's columns into the output.

Common mistakes#

• Using IN with a subquery that can return NULL values. WHERE x NOT IN (SELECT ... ) silently returns zero rows if the subquery produces even one NULL, because comparing anything to NULL is neither true nor false. NOT EXISTS doesn't have this trap and is the safer default for "not in" logic.
• Writing a correlated subquery that could be a plain join, then being surprised it's slow on a large table.
• Forgetting UNION ALL (not plain UNION) inside a recursive CTE's recursive branch — plain UNION de-duplicates on every iteration, which is usually unnecessary overhead and, for some patterns, changes the result.
• Nesting subqueries three or four levels deep instead of naming each step with a CTE — it runs the same, but nobody (including future you) can read it back later.