CTE vs subquery vs view

Same logic, three places, three lifetimes

Why does the form matter if all three produce the same rows? Because lifetime determines reusability and — as the rest of the lesson shows — performance. The same subquery can appear in three structurally different forms. Take “categories whose parent is the root” against categories(id, parent_id):

-- 1. Inline / derived-table subquery: lives in the FROM of one statement
SELECT c.id
FROM categories c
JOIN (SELECT id FROM categories WHERE parent_id IS NULL) roots
  ON c.parent_id = roots.id;

-- 2. CTE: named, still scoped to one statement
WITH roots AS (SELECT id FROM categories WHERE parent_id IS NULL)
SELECT c.id
FROM categories c
JOIN roots ON c.parent_id = roots.id;

-- 3. View: named, stored in the catalog, reusable across many statements
CREATE VIEW roots AS SELECT id FROM categories WHERE parent_id IS NULL;
SELECT c.id FROM categories c JOIN roots ON c.parent_id = roots.id;  -- later, anywhere

The axis that distinguishes them is lifetime and visibility. An inline subquery has no name and exists only at its position in one statement. A CTE has a name but is still scoped to one statement — when the statement ends, the name is gone. A view has a name stored in the catalog: it persists, and any future query can read from it. A scalar subquery is a fourth shape — a subquery in parentheses that returns exactly one row and one column, usable anywhere a single value is expected, like WHERE created_at = (SELECT max(created_at) FROM orders).

When a plain subquery is the clearer choice

CTEs are not automatically better. A subquery in FROM is often clearer when the logic is used exactly once and is small — wrapping a three-line filter in a WITH can add ceremony without adding clarity. A scalar subquery in a SELECT or WHERE is the natural form for “compare each row to one aggregate value”; rewriting it as a CTE usually reads worse. And a correlated subquery — one that references the outer row, like WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id) — has no clean CTE equivalent because it runs per outer row by design. Use the form that names what deserves a name and inlines what doesn’t.

The caveat that points to lesson 03

Here is the part that turned the review argument from style into substance. For most of Postgres’s history, a CTE was an unconditional optimization fence: the planner always evaluated the CTE separately and materialized its result, refusing to push predicates from the outer query down into it. An equivalent inline subquery was not fenced — the planner could flatten it, push filters in, and choose indexes accordingly. So the same logic could run very differently depending on which form you picked, and the CTE form was sometimes dramatically slower.

-- Pre-PG12: this CTE was materialized whole, then filtered — the WHERE
-- could NOT push into the CTE, so it scanned far more rows than needed.
WITH all_events AS (SELECT * FROM events)        -- millions of rows materialized
SELECT * FROM all_events WHERE tenant_id = 7;    -- filter applied AFTER

-- The equivalent subquery let the planner push tenant_id = 7 down to an index.
SELECT * FROM (SELECT * FROM events) e WHERE tenant_id = 7;

Postgres 12 changed this default, and the keywords MATERIALIZED / NOT MATERIALIZED let you control it explicitly. That whole story — what changed, when materializing still helps, and how to read it in EXPLAIN — is lesson 03. For now, the takeaway is: CTE vs subquery is not purely cosmetic; it has historically affected the plan, and on older servers it still does.