CTE vs Subquery: Readability and Plans

Reach for a CTE when the query has distinct, nameable stages that you want to read in order, or when the same intermediate result is needed more than once. A subquery is an expression buried inside a clause, which is fine for one small step but becomes unreadable when you stack three of them inside each other.

The classic case where a CTE wins is a report with a filtering step that has to happen before an aggregate. A subquery version nests the filter inside a derived table inside the FROM clause, and the reader has to work out which part is which. The CTE version names the stages, so the intent reads top to bottom.

The other case is reuse. A subquery has to be repeated if you need it twice, and repeating an aggregate means running it twice. A CTE can be referenced several times, and on engines that materialise CTEs that is a genuine saving.

Where a subquery is better: a single, small filter. WHERE id IN (SELECT id FROM x WHERE flag = 1) is short, obvious, and gives the optimizer maximum freedom to push the predicate around. Wrapping the same thing in a CTE adds a layer without adding clarity.

The performance answer is that there usually is not one. On PostgreSQL and SQLite a non-recursive CTE is inlined and the plans are identical. On MySQL the CTE is materialised by default, which is slower for a single reference and faster for several. What actually changes the plan is the shape of the query — whether a predicate can be pushed into a derived table, and how many times an aggregate is computed — not the choice between the two syntaxes.

Here is the same thing as runnable code:

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL,
  status VARCHAR(20) NOT NULL
);

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

INSERT INTO customers (id, name) VALUES
  (1, 'Ada Lovelace'),
  (2, 'Grace Hopper'),
  (3, 'Alan Turing');

INSERT INTO orders (id, customer_id, amount, status) VALUES
  (1, 1, 120.00, 'shipped'),
  (2, 1, 340.50, 'pending'),
  (3, 2,  75.25, 'shipped'),
  (4, 2,  15.00, 'cancelled');

-- The same calculation written two ways.
SELECT c.name,
       (SELECT SUM(o.amount) FROM orders AS o WHERE o.customer_id = c.id) AS total_spend
FROM customers AS c
ORDER BY c.id;

WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total_spend
  FROM orders
  GROUP BY customer_id
)
SELECT c.name, COALESCE(t.total_spend, 0) AS total_spend
FROM customers AS c
LEFT JOIN customer_totals AS t ON t.customer_id = c.id
ORDER BY c.id;

The two versions compute the same number for customers who have orders, but they differ on the empty case — and that difference is the whole point of the comparison.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(120) NOT NULL
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);

INSERT INTO customers (id, name) VALUES
  (1, 'Ada Lovelace'),
  (2, 'Grace Hopper'),
  (3, 'Alan Turing');

INSERT INTO orders (id, customer_id, amount) VALUES
  (1, 1, 120.00),
  (2, 1, 340.50),
  (3, 2,  75.25);

-- Version A: a scalar subquery in the SELECT list.
SELECT c.id, c.name,
       (SELECT SUM(o.amount) FROM orders AS o WHERE o.customer_id = c.id) AS total_spend
FROM customers AS c
ORDER BY c.id;

-- Version B: a CTE that aggregates once, joined back.
WITH customer_totals AS (
  SELECT customer_id, SUM(amount) AS total_spend
  FROM orders
  GROUP BY customer_id
)
SELECT c.id, c.name, COALESCE(t.total_spend, 0) AS total_spend
FROM customers AS c
LEFT JOIN customer_totals AS t ON t.customer_id = c.id
ORDER BY c.id;

Both return three rows. Ada Lovelace has 460.50 and Grace Hopper has 75.25. Alan Turing has no orders, and here the versions part company: version A gives NULL, because SUM over an empty set is NULL, while version B gives 0.00 because the LEFT JOIN produced no match and the COALESCE substituted a zero.

Neither is wrong; they answer different questions. Version A says "we do not know what Alan spent, because there is no order to sum". Version B says "Alan spent zero". For a revenue report you usually want the second, and for a data-quality report you usually want the first — or, better, you keep the raw value visible alongside the display value so the difference never gets lost.

Version B also has the better cost profile on large data. The CTE aggregates the orders table once and joins the result; version A runs a correlated subquery once per customer. With three customers that is three executions, but with three million customers it is three million, and the CTE version stays proportional to the number of orders.

The readability verdict goes to version B as well, mostly because it separates the two concerns: the CTE computes the per-customer total, and the outer query decides what to do about missing customers. When you need both a total and a count, or a total and a date, the CTE version lets you add another aggregate column without re-running the subquery.