CTEs and subqueries

Every question so far has fitted in one step. Real ones often do not. "Which products beat the average" needs the average first. "How did each month compare to the last" needs the monthly totals first. Both are two-step questions, and this lesson is about giving the first step a name so the second step can read it.

You have two tools for that, and one of them is much easier to read than the other.

WITH gives a result a name

A common table expression, which everyone calls a CTE, is a named query that exists for the duration of the statement:

WITH monthly AS (
  SELECT date_trunc('month', date) AS month, SUM(revenue) AS total
  FROM csv
  GROUP BY month
)
SELECT *
FROM monthly
ORDER BY month

Inside the brackets is a perfectly ordinary query, and it is the monthly summary from lesson 10. Below the brackets, monthly behaves exactly like a table. You can filter it, join it, group it again, or feed it to a window function.

That last one is the reason CTEs matter for reporting. Window functions cannot be nested inside aggregates, so month-over-month growth genuinely cannot be written in a single flat query. You aggregate to months in a CTE, then LAG across the CTE. Two steps, in the order you would explain them to a colleague.

Several CTEs

Separate them with commas, and each may read the ones above it:

WITH by_region AS (
  SELECT region, SUM(revenue) AS total FROM csv GROUP BY region
),
overall AS (
  SELECT SUM(revenue) AS grand FROM csv
)
SELECT r.region, ROUND(100 * r.total / o.grand, 2) AS pct_of_total
FROM by_region r, overall o
ORDER BY pct_of_total DESC

Note the comma between the two files in the FROM clause. That is a cross join, and it is safe here precisely because overall is a single row: four regions times one row is four rows. Against anything with more than one row, a cross join multiplies, and it is one of the few ways to accidentally turn a small query into a very slow one.

A query with four or five well-named CTEs reads like a paragraph. A query with four levels of nested subqueries reads like a puzzle. When you inherit the second kind, converting it to the first, one level at a time, is usually the fastest way to understand it, and each step is mechanical enough to be safe.

Scalar subqueries

A parenthesised query returning exactly one row and one column is a value, and can go wherever a value can:

SELECT date, product, revenue
FROM csv
WHERE revenue > (SELECT AVG(revenue) FROM csv)

The inner query computes one number, the outer compares every row against it. This cannot be written with a plain WHERE, because you cannot use an aggregate of the whole table as a row-level condition. If the subquery returns more than one row you get an error; if it returns none you get NULL, and the comparison quietly matches nothing.

A subquery can also produce a whole table and sit in the FROM clause, which is what people did before CTEs existed. The pattern still appears everywhere: FROM (SELECT ...) AS t. It works fine. A CTE says the same thing with the definition at the top instead of buried in the middle, which is the only reason I prefer it.

IN with a subquery

A subquery returning one column and many rows works as the list for IN:

SELECT *
FROM csv
WHERE product IN (
  SELECT product FROM csv GROUP BY product HAVING SUM(units) > 200
)

Find the products that clear a threshold, then return every row belonging to them. This is a genuinely two-pass question and the shape is worth memorising: an aggregate in the inner query chooses the keys, the outer query fetches the detail.

The warning from lesson 4 comes due here. If the inner query can return a NULL, then NOT IN returns nothing at all, for every row, silently. Use NOT EXISTS or a LEFT JOIN anti-join instead, both of which behave sensibly with nulls. Plain IN is unaffected.

Does it cost anything?

Almost never. A CTE is not a temporary table you are paying to build; in DuckDB, Postgres 12 and later, and every modern warehouse, the planner folds it into the surrounding query and optimises the whole thing together. Older Postgres treated a CTE as an optimisation fence, which is where the folklore about CTEs being slow comes from. On this engine, write whichever form is clearest.

One genuine exception: if a CTE is referenced several times and is expensive, some engines recompute it each time. DuckDB and Postgres both offer MATERIALIZED to force it to be computed once. You will know when you need it, because the query will be slow in a way that tracks the number of references.

What to look up next

Look up recursive CTEs, written WITH RECURSIVE. They let one query walk a hierarchy, such as an org chart or a bill of materials, to any depth. It is the one genuinely mind-bending corner of SQL and it is worth an afternoon.