Subqueries and CTEs: Asking a Question Inside a Question

Some questions can't be answered in one pass. "Which hive produced the most honey?" needs the maximum honey figure before it can find the hive that matches it. "Which keepers are above average?" needs the average before it can compare anyone to it. SQL handles this the same way you'd handle it on paper: work out the smaller number first, then use it.

That's what a subquery is. A CTE does the same job with a different shape. We'll reuse the beekeeping co-op from the joins article, so the data below should look familiar, with one addition that makes the later examples worth running.

The Data, Recapped

See the tables used for this article here.
beekeepers

Grace Achieng is new. She's Brian's apprentice, which gives the co-op a three-generation mentorship line: Amara trained Brian, and Brian is now training Grace. We'll need that depth later.

hives

harvests

What a Subquery Is

A subquery is a query nested inside another one, wrapped in parentheses. The database runs it, gets a result, and hands that result to the outer query as if it had been typed there directly. Depending on where it sits, a subquery can return a single value, a list of values, or an entire result set standing in for a table.

A scalar subquery: finding the single best harvest

SELECT hive_id, harvest_date, honey_kg
FROM harvests
WHERE honey_kg = (SELECT MAX(honey_kg) FROM harvests);

Result:

The inner query, SELECT MAX(honey_kg) FROM harvests, runs first and reduces to one number: 15.0. The outer query never sees the subquery at all once it's been resolved. A subquery that returns exactly one value is called a scalar subquery, and it can sit almost anywhere a literal number could.

A subquery with IN: hives that have never been harvested

SELECT hive_id, location
FROM hives
WHERE hive_id NOT IN (SELECT hive_id FROM harvests);

Result:

Here the inner query returns a list, (101, 101, 102, 999), and NOT IN checks each hive against that list. Hives 103 and 104 never appear in harvests, so they survive the filter. One thing worth flagging is that NOT IN turns dangerous the moment the subquery's list can contain a NULL, since NOT IN against a list containing NULL returns no rows at all, silently. harvests.hive_id is never NULL here, so it's safe, but NOT EXISTS is the safer default habit for this exact pattern once nullable columns get involved.

A correlated subquery: each hive's most recent harvest

SELECT h1.hive_id, h1.harvest_date, h1.honey_kg
FROM harvests h1
WHERE h1.harvest_date = (
    SELECT MAX(h2.harvest_date)
    FROM harvests h2
    WHERE h2.hive_id = h1.hive_id
);

Result:

This one is different in kind, not just in syntax. The inner query references h1.hive_id, a column from the outer query, so it can't run once and be done. The database runs it fresh for every row in harvests, filtered to that row's hive. Hive 101 has two harvest dates, and this picks the later one. This "latest record per group" shape shows up constantly: latest login per user, latest price per product, latest reading per sensor.

A correlated subquery in SELECT: hive count per keeper

SELECT b.name,
    (SELECT COUNT(*) FROM hives h WHERE h.keeper_id = b.keeper_id) AS hive_count
FROM beekeepers b;

Result:

Same correlation, different location. The subquery sits in the column list this time, running once per row of beekeepers and returning a single number for each. David and Grace get 0, not NULL, because COUNT(*) on an empty match still counts to zero rather than finding nothing.

A subquery in FROM: a derived table

SELECT h.location, hive_totals.total_honey_kg
FROM hives h
JOIN (
    SELECT hive_id, SUM(honey_kg) AS total_honey_kg
    FROM harvests
    GROUP BY hive_id
) AS hive_totals ON h.hive_id = hive_totals.hive_id
WHERE hive_totals.total_honey_kg > 10;

Result:

The subquery in the FROM clause builds a small temporary table, hive_totals, that the outer query joins against like any other table. Note that hive 999's total of 5.0 kg quietly vanishes here: it has no matching row in hives, so the JOIN drops it before the WHERE clause even runs. A derived table behaves exactly like a real one for the rest of the query, it just doesn't exist anywhere except for the duration of this statement.

What a CTE Is

A CTE, common table expression, does the same job as a subquery i.e. it names a temporary result set for one query to use. The difference is where you write it. Instead of nesting it inside the query, you declare it up front with WITH, give it a name, and then write the rest of the query as if that name were a real table.

Rewriting the derived-table example above as a CTE:

WITH hive_totals AS (
    SELECT hive_id, SUM(honey_kg) AS total_honey_kg
    FROM harvests
    GROUP BY hive_id
)
SELECT h.location, hive_totals.total_honey_kg
FROM hives h
JOIN hive_totals ON h.hive_id = hive_totals.hive_id
WHERE hive_totals.total_honey_kg > 10;

Result:

Identical output, identical execution in most modern databases. The value here isn't a new capability, it's that you now read the query top to bottom: first, here's what hive_totals means; then, here's what to do with it. Once a query needs three or four steps, that ordering stops being a nicety and starts being the difference between a query you can debug and one you can't.

Where CTEs pull ahead: using the same result twice

Go back to an earlier goal: find keepers whose total honey is above the co-op average. Written with nested subqueries alone, the "total honey per keeper" calculation has to appear twice, once to list it, once to average it:

SELECT name, total_honey_kg
FROM (
    SELECT b.name, COALESCE(SUM(hv.honey_kg), 0) AS total_honey_kg
    FROM beekeepers b
    LEFT JOIN hives h ON b.keeper_id = h.keeper_id
    LEFT JOIN harvests hv ON h.hive_id = hv.hive_id
    GROUP BY b.name
) AS keeper_totals
WHERE total_honey_kg > (
    SELECT AVG(total_honey_kg)
    FROM (
        SELECT b.name, COALESCE(SUM(hv.honey_kg), 0) AS total_honey_kg
        FROM beekeepers b
        LEFT JOIN hives h ON b.keeper_id = h.keeper_id
        LEFT JOIN harvests hv ON h.hive_id = hv.hive_id
        GROUP BY b.name
    ) AS t
);

It works, but the five-line calculation is now duplicated word for word. Change one join later and you have to remember to change it twice. A CTE removes the duplication by naming the calculation once and referencing it as often as needed:

WITH keeper_totals AS (
    SELECT b.name, COALESCE(SUM(hv.honey_kg), 0) AS total_honey_kg
    FROM beekeepers b
    LEFT JOIN hives h ON b.keeper_id = h.keeper_id
    LEFT JOIN harvests hv ON h.hive_id = hv.hive_id
    GROUP BY b.name
)
SELECT name, total_honey_kg
FROM keeper_totals
WHERE total_honey_kg > (SELECT AVG(total_honey_kg) FROM keeper_totals);

Result:

The co-op's average sits at 7.3 kg across all five keepers, including the three who've harvested nothing yet. Amara and Brian clear it; everyone else doesn't. Same logic as the nested version, defined once, and one CTE now stands in for what would otherwise be two identical subqueries.

Where a subquery can't follow: recursive CTEs

WITH RECURSIVE lets a CTE reference itself, which makes it the only one of the two that can walk a chain of unknown length. The mentorship data is exactly that shape: Amara trained Brian, Brian is training Grace, and nothing in the schema tells you in advance how many links that chain will have.

WITH RECURSIVE mentorship_chain AS (
    -- Anchor: keepers with no mentor, the top of each line
    SELECT keeper_id, name, mentor_id, name AS lineage, 0 AS depth
    FROM beekeepers
    WHERE mentor_id IS NULL

    UNION ALL

    -- Recursive step: find apprentices of keepers already in the chain
    SELECT b.keeper_id, b.name, b.mentor_id,
           mc.lineage || ' -> ' || b.name, mc.depth + 1
    FROM beekeepers b
    JOIN mentorship_chain mc ON b.mentor_id = mc.keeper_id
)
SELECT name, depth, lineage
FROM mentorship_chain
ORDER BY depth, name;

Result:

The anchor half of the query finds Amara and David, the two keepers with no mentor. The recursive half then runs repeatedly: first it finds anyone whose mentor is Amara or David (Brian and Chiara), then it runs again on those new rows and finds anyone whose mentor is Brian or Chiara (Grace), then it runs once more, finds nobody new, and stops. Three generations, discovered without knowing in advance how many there'd be. No plain subquery can do this; a subquery is a single, fixed level of nesting, and there's no way to tell it "keep going until you run out of matches." That open-ended repetition is what RECURSIVE adds.

Note: (The || operator concatenates text in PostgreSQL and SQLite. SQL Server uses +, and MySQL uses CONCAT(), so adjust that one line for your engine; everything else here is standard SQL.)

Subquery or CTE: How to Choose

The two aren't really competitors. A subquery is a value or a table, dropped exactly where you need it. A CTE is a name, declared once, that the rest of the query can refer to as often as it likes, and in the recursive case, refer to before it's even finished being built. Most real queries end up mixing both: a CTE or two to name the steps that matter, and small scalar subqueries wherever a single value needs to be looked up in passing.

One caution before you assume a CTE is always the faster choice: whether a database materializes a CTE (runs it once, stores the result) or inlines it (folds it into the surrounding query, like a subquery) depends on the engine and its version. PostgreSQL 12 and later will inline a non-recursive CTE when it can; older versions treated every CTE as an optimization fence. When performance is on the line, check your engine's execution plan rather than assuming either form is faster by default.

Try It Yourself

Add a sixth beekeeper as Grace's apprentice, rerun the recursive query, and watch the chain grow by one row without changing a single line of SQL. That's the property no ordinary subquery can offer, and it's the clearest way to feel the difference between the two.