Subqueries, CTEs and set operations

A subquery is a query used as a value, a set, or a table. Which of the three it is determines where it can appear and what it costs.

Scalar subquery: used as a value

Must return at most one row and one column.

SELECT id, cents,
       cents - (SELECT AVG(cents) FROM product) AS vs_average
FROM product;

If it returns more than one row you get a runtime error — in both engines — which is a real hazard when the data grows into a case you did not consider. A scalar subquery that returns no rows yields NULL instead, so the arithmetic quietly becomes NULL rather than failing. Two different failure modes from one construct.

IN / EXISTS: used as a set

SELECT * FROM customer
WHERE id IN (SELECT customer_id FROM "order" WHERE status = 'paid');

Prefer EXISTS when the subquery could contain NULL, for the reason in lesson 2. Prefer IN when the list is small and literal — it reads better.

A correlated subquery references the outer row, so conceptually it runs per row:

SELECT c.id,
       (SELECT COUNT(*) FROM "order" o WHERE o.customer_id = c.id) AS orders
FROM customer c;

Both planners can often rewrite this into a join or a grouped aggregate, so “runs per row” is a mental model, not a promise about execution. But the rewrite is not guaranteed, and this is the shape that turns into a per-row lookup on a large table. When you need several such counts, one LEFT JOIN onto a grouped subquery beats five correlated subqueries.

Derived table: used as a table

A subquery in FROM. MySQL requires an alias; PostgreSQL also requires one.

SELECT o.id, g.goods
FROM "order" o
JOIN (
  SELECT order_id, SUM(unit_cents * quantity) AS goods
  FROM order_item
  GROUP BY order_id
) g ON g.order_id = o.id;         -- the alias g is mandatory

This is the aggregate-then-join shape from lesson 3, and it is the single most useful subquery pattern.

WITH: the same thing, named

A common table expression is a derived table given a name up front, which lets you read the query top to bottom instead of inside out.

WITH goods AS (
  SELECT order_id, SUM(unit_cents * quantity) AS cents
  FROM order_item
  GROUP BY order_id
),
big AS (
  SELECT order_id, cents FROM goods WHERE cents > 100000
)
SELECT o.id, c.full_name, big.cents
FROM big
JOIN "order" o    ON o.id = big.order_id
JOIN customer c   ON c.id = o.customer_id;

MySQL supports WITH from 8.0. Before that, a derived table was the only option.

The optimisation fence

This is the part people carry stale knowledge about.

So the old advice “CTEs are slow in PostgreSQL” was true and is no longer the default. And the opposite advice — “CTEs are free” — is also wrong, because materialisation still happens when the CTE is referenced twice.

Occasionally you want the fence:

-- Force the CTE to run once, exactly as written
WITH expensive AS MATERIALIZED (
  SELECT ... something with a costly function ...
)
SELECT * FROM expensive WHERE id = 42;

Without MATERIALIZED, PostgreSQL 12+ may push id = 42 inside and run the costly function on fewer rows — usually good, occasionally the opposite of what you measured.

Recursive CTEs

Both engines support WITH RECURSIVE, and the shape is identical: a non-recursive seed, UNION ALL, then a term that references the CTE itself.

-- A date series: one row per day in January 2026
WITH RECURSIVE days(d) AS (
  SELECT DATE '2026-01-01'
  UNION ALL
  SELECT d + 1 FROM days WHERE d < DATE '2026-01-31'
)
SELECT d FROM days;

MySQL writes the increment differently:

WITH RECURSIVE days(d) AS (
  SELECT DATE '2026-01-01'
  UNION ALL
  SELECT d + INTERVAL 1 DAY FROM days WHERE d < DATE '2026-01-31'
)
SELECT d FROM days;

The termination condition is yours to get right. Omit it and PostgreSQL runs until it exhausts memory or disk; MySQL stops at cte_max_recursion_depth, default 1000, with an error. MySQL’s default is the friendlier failure.

PostgreSQL has a much simpler tool for the specific case of a series: generate_series('2026-01-01'::date, '2026-01-31'::date, '1 day'). MySQL has no equivalent, which is why the recursive form is worth knowing.

The real use is hierarchy — a category tree, a reporting line, an order that replaces a previous order:

WITH RECURSIVE chain AS (
  SELECT id, replaces_order_id, 1 AS depth
  FROM "order" WHERE id = 1000
  UNION ALL
  SELECT o.id, o.replaces_order_id, chain.depth + 1
  FROM "order" o JOIN chain ON o.id = chain.replaces_order_id
)
SELECT * FROM chain;

(replaces_order_id is not in our schema — it is the shape that matters.)

Guard against cycles. A row whose parent chain loops makes this run forever; the usual fix is to accumulate the visited ids in an array (PostgreSQL) or a concatenated string (MySQL) and exclude them.

Set operations

SELECT country FROM customer
EXCEPT
SELECT country FROM customer WHERE created_at >= '2026-01-01';
-- countries that have customers, but none new this year

Rules that apply to all of them: the two sides must have the same number of columns with compatible types, the column names come from the first branch, and ORDER BY belongs at the very end, applying to the combined result.

UNION ALL unless you mean otherwise. UNION has to deduplicate, which means sorting or hashing every row of both inputs. If you know the branches are disjoint — and you usually do, because that is why you split them — UNION ALL skips all of that work.

LATERAL: a subquery that sees the current row

LATERAL lets a derived table reference columns from earlier items in the same FROM. Both PostgreSQL and MySQL 8.0.14+ support it.

-- The three most recent orders per customer
SELECT c.id, o.id, o.placed_at
FROM customer c
CROSS JOIN LATERAL (
  SELECT id, placed_at FROM "order"
  WHERE customer_id = c.id            -- only legal because of LATERAL
  ORDER BY placed_at DESC LIMIT 3
) o;

Without LATERAL, c.id is not visible inside the subquery. This “top N per group” problem also has a window-function answer, which is the next lesson — the LATERAL form is often faster when N is small and an index supports the ORDER BY.

What to take away

• Scalar subqueries fail loudly on multiple rows and silently on zero.
• A derived table needs an alias, in both engines.
• CTEs are named subqueries. PostgreSQL 12+ inlines single-use ones; MATERIALIZED restores the old fence.
• WITH needs MySQL 8.0; INTERSECT / EXCEPT need 8.0.31.
• UNION ALL unless you actually need deduplication.
• LATERAL is how a subquery in FROM sees the current row.