beginnerintermediateadvanced

PostgreSQL · Section 4

Subqueries and CTEs

Level: intermediate
Read: 34 min
Concepts: 6

The scalar/correlated vocabulary every subquery discussion assumes, why EXISTS is a pure existence check immune to a NULL trap that silently breaks NOT IN, and how WITH names a piece of query logic — including the recursive form that walks a hierarchy — along with when that naming helps clarity versus when it interacts with the planner.

PostgreSQL overview

Learn PostgreSQL Tutorial - Full Course for BeginnersfreeCodeCamp.org

What is true here

A subquery is independently scalar-or-multi-row and correlated-or-non-correlated — two separate axes.
EXISTS never inspects the subquery's selected values, only whether any row matched.
NOT IN against a subquery result containing any NULL silently returns zero rows — NOT EXISTS does not have this problem.
WITH name AS (...) names a subquery for one statement; it does not persist like a view.
WITH RECURSIVE re-joins its recursive term against only the previous round's output, terminating when a round adds nothing new.

What you will be able to do

Distinguish a scalar subquery from a correlated one, and know why the distinction matters
Choose NOT EXISTS over NOT IN whenever NULL is a possibility
Name a multi-step query with WITH for readability and controlled reuse
Write a WITH RECURSIVE query to walk a hierarchy, with a cycle guard when the data is not a strict tree

From an inline subquery to a named, reusable, self-referencing one

Inline subquery

scalar or correlated, written in place

EXISTS / NOT EXISTS

a yes/no subquery, NULL-safe

CTE (WITH)

named, scoped to one statement

WITH RECURSIVE

a CTE that references itself

Subquery vocabulary

What "scalar" and "correlated" mean, and why they are independent properties.

Scalar, Correlated and Non-Correlated Subqueries

coreintermediate

A scalar subquery returns exactly one value. A non-correlated subquery runs once, independent of the outer query. A correlated subquery references a column from the outer query, so PostgreSQL conceptually re-evaluates it for each outer row.

Think of it as

These are two independent axes, not three flavors of one thing. "Scalar vs multi-row" is about shape (how many values come back). "Correlated vs non-correlated" is about dependency (does it reference the outer query). A subquery can be scalar and non-correlated (a single independent lookup), scalar and correlated (a per-row lookup), or multi-row and either.

sql

-- scalar, non-correlated
SELECT * FROM products WHERE price > (SELECT avg(price) FROM products);

-- scalar, correlated
SELECT c.name, (SELECT count(*) FROM orders o WHERE o.customer_id = c.id) AS n
FROM customers c;

What we're doing: Compare a non-correlated scalar subquery (an overall average) against a correlated one (a per-customer count) in the same query.

subquery_kinds.sqlsql

CREATE TABLE customers (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT, total NUMERIC);

INSERT INTO customers (name) VALUES ('Ada'), ('Grace');
INSERT INTO orders (customer_id, total) VALUES (1, 100), (1, 50), (2, 30);

SELECT c.name,
    (SELECT count(*) FROM orders o WHERE o.customer_id = c.id) AS order_count,
    (SELECT avg(total) FROM orders) AS overall_avg
FROM customers c;

7: Correlated: o.customer_id = c.id references the outer row c, so this count differs per customer.
8: Non-correlated: no reference to c anywhere — this is the same single number for every row.

Output

name  | order_count | overall_avg
------+-------------+------------
Ada   |           2 | 60.0000000000000000
Grace |           1 | 60.0000000000000000
(2 rows)

Why this works: order_count changes per row because its subquery filters on c.id, a column from the outer query — PostgreSQL must conceptually know which customer it is currently looking at to evaluate it, which is the definition of correlated. overall_avg is identical on every row precisely because nothing inside it references c at all; it is answering a fixed question ("what is the average across all orders") that does not depend on which customer row it happens to sit next to.

Using a multi-row subquery where a scalar is expected

Wrong

sql

SELECT * FROM customers WHERE id = (SELECT customer_id FROM orders);
-- ERROR: more than one row returned by a subquery used as an expression

Better

sql

SELECT * FROM customers WHERE id IN (SELECT customer_id FROM orders);
-- IN accepts a multi-row subquery

What you see: The query runs fine while the orders table happens to hold rows for only one customer, then breaks the moment a second customer places an order — the exact same query, now failing on more data.

Why: = expects exactly one value on each side; a subquery that returns more than one row cannot satisfy that, and PostgreSQL only discovers the violation at execution time based on how many rows actually come back, not at parse time. IN is designed for a multi-row right-hand side and does not carry this fragility, which is why it is the correct choice whenever the subquery's row count is not guaranteed to be exactly one.

Non-correlated vs correlated — does the subquery see the outer row?

Non-correlated

+no reference to the outer query
+conceptually runs once, result reused
+e.g. (SELECT max(price) FROM products)

Correlated

−references a column from the outer query
−conceptually re-evaluated per outer row
−e.g. (SELECT count(*) FROM orders o WHERE o.customer_id = c.id)

Two independent axes: shape and dependency

Two independent axes: shape and dependency
Subquery	Shape	Depends on outer row?
(SELECT max(price) FROM products)	scalar	no — non-correlated
(SELECT count(*) FROM orders o WHERE o.customer_id = c.id)	scalar	yes — correlated
(SELECT id FROM products WHERE category = 'books')	multi-row	no — non-correlated

Together

sql

SELECT name, (SELECT count(*) FROM orders o WHERE o.customer_id = c.id) AS order_count
FROM customers c;

Remember: Scalar vs multi-row is about shape; correlated vs non-correlated is about whether the subquery references the outer row — a subquery can be any combination of the two.

Existence checks and their NULL trap

EXISTS as a pure yes/no test, and the specific NOT IN failure mode it avoids.

Using EXISTS and NOT EXISTS Appropriately

standardintermediate

EXISTS checks whether a correlated subquery returns at least one row — it never inspects the returned values, only whether any row came back. NOT EXISTS is the negation: true when the subquery returns nothing for the current outer row.

Think of it as

EXISTS is a pure yes/no test, not a value comparison — the subquery inside it is usually written as SELECT 1 or SELECT * precisely because the actual columns are irrelevant, only row existence matters. This is what makes EXISTS immune to the NULL-comparison problems that plague value-based subqueries: it never compares a value to NULL, it only asks "did anything match."

sql

SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

What we're doing: Use EXISTS to find customers with at least one order, and NOT EXISTS to find customers with none.

exists_demo.sqlsql

CREATE TABLE customers (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT, total NUMERIC);

INSERT INTO customers (name) VALUES ('Ada'), ('Grace'), ('Linus');
INSERT INTO orders (customer_id, total) VALUES (1, 100);

SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

4–5: Only Ada (customer 1) has placed an order.
7–8: EXISTS: true only for Ada, since only her subquery finds a matching order row.
10–11: NOT EXISTS: true for Grace and Linus, since their subqueries find nothing.

Output

name
----
Ada
(1 row)

name
-----
Grace
Linus
(2 rows)

Why this works: For each customer row, PostgreSQL runs the correlated subquery with that customer's id substituted in, and EXISTS asks only "did this return anything" — it can stop at the first match rather than scanning every order, which is why EXISTS is typically efficient even without inspecting values. NOT EXISTS is the direct negation of the same check, and because it never compares an order value to NULL, it does not inherit the NOT IN pitfall of a NULL silently making the whole condition unknown.

Writing EXISTS as if it filters on the subquery's selected columns

Wrong

sql

SELECT c.name FROM customers c
WHERE EXISTS (SELECT total FROM orders o WHERE o.customer_id = c.id AND total > 1000000);
-- works, but the selected column "total" is misleadingly irrelevant to what EXISTS checks

Better

sql

SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 1000000);
-- SELECT 1 makes it clear only row existence matters, not the selected value

What you see: A reader assumes changing the selected column changes what EXISTS checks, then is confused when swapping total for id (or anything else) produces an identical result.

Why: EXISTS discards whatever the subquery's SELECT list actually produces — it only cares whether the subquery's WHERE clause matched any row at all. Writing SELECT 1 is a convention that makes this explicit to readers; selecting a real column works identically but invites the false impression that the column's value participates in the outer WHERE, when the filtering logic lives entirely in the subquery's own WHERE clause.

EXISTS vs a value comparison

EXISTS vs a value comparison
Approach	What it checks
WHERE id IN (SELECT customer_id FROM orders)	compares id against a list of values
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)	checks only whether any matching row exists

Together

sql

SELECT c.name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Remember: EXISTS is a pure yes/no test — it never inspects the subquery's selected values, only whether any row matched, which is why it sidesteps NULL comparison pitfalls entirely.

IN vs EXISTS and NULL-Related Edge Cases

coreintermediate

IN and EXISTS often look interchangeable, but NOT IN against a subquery containing even one NULL silently returns zero rows for every outer row — not an error, just an empty result. NOT EXISTS does not have this problem, which is why it is the safer default for "rows with no match."

Think of it as

IN and NOT IN are defined in terms of equality comparisons against every value the subquery returns, and any comparison against NULL evaluates to NULL (unknown), not false. NOT IN needs every comparison to be true to include a row — one unknown comparison poisons the whole AND chain, so a single NULL in the subquery's result silently defeats the entire NOT IN condition for every row, not just the one involving the NULL.

sql

-- dangerous if orders.customer_id can be NULL
SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);

-- safe regardless of NULLs
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

What we're doing: Reproduce the NOT IN + NULL silent-failure and show NOT EXISTS returning the correct result on the exact same data.

not_in_null_trap.sqlsql

CREATE TABLE customers (id SERIAL PRIMARY KEY, name TEXT);
CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT);

INSERT INTO customers (name) VALUES ('Ada'), ('Grace');
INSERT INTO orders (customer_id) VALUES (1);
INSERT INTO orders (customer_id) VALUES (NULL);
-- an order row exists with no customer_id -- perhaps a guest checkout

SELECT name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);
-- expected: Grace (no orders). actual: 0 rows.

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
-- correctly returns Grace

6: One order row has NULL customer_id — a realistic case, e.g. a guest checkout not yet linked to an account.
9–10: NOT IN's subquery result is (1, NULL). id NOT IN (1, NULL) is NULL for every id, including Grace's — the WHERE clause discards every row.
12–14: NOT EXISTS never touches the NULL — it just checks row existence per customer and correctly finds Grace has none.

Output

name
----
(0 rows)

name
-----
Grace
(1 row)

Why this works: id NOT IN (1, NULL) expands to id <> 1 AND id <> NULL — and id <> NULL is NULL (unknown) for every id, no exceptions, because nothing can be confirmed unequal to an unknown value. AND with any NULL operand is NULL unless the other operand is already false, so the whole condition becomes NULL for every customer, and WHERE discards rows where the condition is not true — silently producing zero rows instead of an error that would at least reveal the problem.

Reaching for NOT IN with a subquery column that is not guaranteed NOT NULL

Wrong

sql

SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);
-- silently returns 0 rows if customer_id ever contains NULL

Better

sql

SELECT c.* FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
-- correct regardless of NULLs, and reads just as clearly

What you see: A "find rows with no match" query works correctly in development against clean data, then quietly starts returning zero rows in production the moment the subquery's column picks up even one NULL value — no error, no warning, just a wrong empty result.

Why: NOT IN's NULL sensitivity is not a bug being triggered, it is standard three-valued SQL logic operating exactly as specified — the danger is that nothing about the query's syntax signals this risk, and it only manifests once real-world data introduces a NULL that a clean test dataset never had. NOT EXISTS sidesteps the entire class of problem structurally, which is why style guides recommend it as the default over NOT IN whenever the subquery's column nullability is not airtight.

Same intent, opposite behavior when the subquery contains a NULL

NOT IN

+x NOT IN (1, 2, NULL) evaluates to NULL
+the WHERE clause discards NULL rows too
+result: zero rows, silently, no error

NOT EXISTS

−never compares a value against NULL
−only checks whether a matching row exists
−result: correctly finds every unmatched row

NOT IN vs NOT EXISTS when the subquery can contain NULL

NOT IN vs NOT EXISTS when the subquery can contain NULL
Approach	Behavior when subquery contains a NULL
WHERE id NOT IN (SELECT customer_id FROM orders)	returns 0 rows if any customer_id is NULL — a silent bug
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id)	unaffected — correctly finds unmatched rows

Together

sql

-- orders.customer_id contains a NULL somewhere
SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);
-- returns ZERO rows, even for customers who clearly have no orders

Remember: NOT IN against a subquery containing any NULL returns zero rows for every outer row, silently — NOT EXISTS is immune to this and is the safer default for "rows with no match."

Naming logic with WITH

Common Table Expressions, their recursive form, and the judgment call between clarity and optimization.

Common Table Expressions Using WITH

coreintermediate

WITH name AS (SELECT ...) defines a named, temporary result set that the rest of the statement can reference like a table. It exists only for the duration of that one statement.

Think of it as

A CTE is a subquery given a name and pulled to the top of the statement, purely for readability and reuse within that statement — it does not create a persistent object the way a view does. Think of it as a local variable for a query: define it once with WITH, then reference it by name as many times as needed in the main query.

sql

WITH big_orders AS (
    SELECT * FROM orders WHERE total > 100
)
SELECT customer_id, count(*) FROM big_orders GROUP BY customer_id;

What we're doing: Define one CTE and reference it twice in the main query, then chain a second CTE off the first.

cte_demo.sqlsql

CREATE TABLE orders (id SERIAL PRIMARY KEY, customer_id INT, total NUMERIC);

INSERT INTO orders (customer_id, total) VALUES (1, 150), (1, 40), (2, 200);

WITH big_orders AS (
    SELECT * FROM orders WHERE total > 100
),
big_order_customers AS (
    SELECT DISTINCT customer_id FROM big_orders
)
SELECT customer_id FROM big_order_customers;

5–7: big_orders is scoped to orders with total > 100 — customers 1 and 2 both qualify (150 and 200).
8–10: big_order_customers chains off big_orders, referencing it by name just like a real table.
11: The main query references the second CTE, which itself depends on the first.

Output

customer_id
-----------
          1
          2
(2 rows)

Why this works: WITH gives each subquery a name that the rest of the statement — including a later CTE in the same WITH clause — can reference exactly like a real table, without PostgreSQL creating any object that outlives the statement. Chaining big_order_customers off big_orders reads top-to-bottom the way the logic is actually reasoned about, instead of nesting big_orders as an inline subquery three levels deep inside big_order_customers's own subquery.

Expecting a CTE to persist or be reusable across separate statements

Wrong

sql

WITH big_orders AS (SELECT * FROM orders WHERE total > 100)
SELECT count(*) FROM big_orders;

SELECT * FROM big_orders;
-- ERROR: relation "big_orders" does not exist

Better

sql

CREATE VIEW big_orders AS SELECT * FROM orders WHERE total > 100;
SELECT count(*) FROM big_orders;
SELECT * FROM big_orders;
-- both work -- a view persists across statements

What you see: A query referencing a previously-defined CTE by name in a new statement fails with "relation does not exist," even though the exact same name worked moments earlier.

Why: A CTE's WITH clause and every name it defines belong to exactly one statement — PostgreSQL discards it the instant that statement finishes, by design, since it was never meant to be a persistent database object. Reaching for CREATE VIEW is the correct fix whenever the same named result set needs to survive into a second, separate statement.

A CTE feeding the main query

WITH big_orders AS (...)

named result set

Main query

references big_orders by name

Result

CTE exists only for this statement

CTE vs subquery vs view

CTE vs subquery vs view
Construct	Scope	Reusable within one query?
Inline subquery	one place it is written	no — must repeat it to reuse
CTE (WITH)	one statement	yes — reference the name repeatedly
View	persists until dropped	yes, across any future query

Together

sql

WITH big_orders AS (SELECT * FROM orders WHERE total > 100)
SELECT count(*) FROM big_orders WHERE total > 500;

Remember: A CTE is a named subquery scoped to one statement — reusable within that statement, but gone the moment it finishes, unlike a view.

Recursive CTEs and When They Are Appropriate

standardadvanced

A WITH RECURSIVE CTE has two parts: a base case that seeds the starting rows, and a recursive part that repeatedly joins back to the CTE's own output, stopping automatically when a round produces no new rows. It is the standard way to walk a hierarchy, like an org chart or category tree.

Think of it as

A recursive CTE is a loop expressed declaratively: run the base case once, then repeatedly run the recursive term against whatever the previous round produced, UNION-ing results together, until a round adds nothing new. The recursive term never sees the full accumulated result — only the rows the immediately preceding round produced — which is what makes it terminate rather than reprocess the same rows forever.

sql

WITH RECURSIVE cte_name AS (
    <base case>
    UNION ALL
    SELECT ... FROM cte_name JOIN ...
)
SELECT * FROM cte_name;

What we're doing: Walk an employee hierarchy top-down from one manager to find every employee under them, at any depth.

org_chart.sqlsql

CREATE TABLE employees (id SERIAL PRIMARY KEY, name TEXT, manager_id INT);

INSERT INTO employees (name, manager_id) VALUES ('Ada', NULL);
INSERT INTO employees (name, manager_id) VALUES ('Grace', 1);
INSERT INTO employees (name, manager_id) VALUES ('Linus', 1);
INSERT INTO employees (name, manager_id) VALUES ('Margaret', 2);
-- Margaret reports to Grace, who reports to Ada -- two levels deep

WITH RECURSIVE subordinates AS (
    SELECT id, name, manager_id FROM employees WHERE id = 1
    UNION ALL
    SELECT e.id, e.name, e.manager_id
    FROM employees e
    JOIN subordinates s ON e.manager_id = s.id
)
SELECT name FROM subordinates WHERE id != 1;

6–7: Margaret is two levels below Ada — a strict UNION ALL of direct reports would miss her.
10: Base case: just Ada, id 1 — round zero.
12–14: Recursive term joins employees to subordinates' most recent round: round 1 finds Grace and Linus (direct reports of Ada), round 2 finds Margaret (reports to Grace, now in subordinates), round 3 finds nothing new and recursion stops.

Output

name
--------
Grace
Linus
Margaret
(3 rows)

Why this works: Each round of the recursive term only joins against rows the previous round added — not the entire accumulated result — so round 1 sees only Ada and finds her direct reports (Grace, Linus), round 2 sees only Grace and Linus and finds Margaret through Grace, and round 3 sees only Margaret, who has no reports, producing zero new rows and ending the recursion. This round-by-round join against "the prior round only" is exactly what makes an arbitrarily deep hierarchy resolvable without knowing its depth in advance.

Writing a recursive CTE over a graph with cycles and no guard

Wrong

sql

WITH RECURSIVE paths AS (
    SELECT from_id, to_id FROM edges WHERE from_id = 1
    UNION ALL
    SELECT e.from_id, e.to_id FROM edges e JOIN paths p ON e.from_id = p.to_id
)
SELECT * FROM paths;
-- if the graph has a cycle (A -> B -> A), this never terminates

Better

sql

WITH RECURSIVE paths AS (
    SELECT from_id, to_id, ARRAY[from_id] AS visited FROM edges WHERE from_id = 1
    UNION ALL
    SELECT e.from_id, e.to_id, p.visited || e.to_id
    FROM edges e JOIN paths p ON e.from_id = p.to_id
    WHERE NOT e.to_id = ANY(p.visited)
)
SELECT * FROM paths;

What you see: The query runs indefinitely (or until statement_timeout or an out-of-memory error) instead of returning a result, because the same cycle of rows keeps producing "new" rows forever.

Why: A tree-shaped hierarchy (employees/managers) naturally terminates because no employee can be their own ancestor, but a general graph can contain a cycle where following edges eventually returns to an already-visited node — and UNION ALL does not deduplicate across rounds, so that cycle regenerates the same rows every round forever. An explicit visited-nodes array with a WHERE NOT ... = ANY(visited) guard breaks the cycle by refusing to revisit a node, which a strict hierarchy never needed but a general graph always does.

The two required parts of WITH RECURSIVE

The two required parts of WITH RECURSIVE
Part	Role
Base case	seeds the starting row(s), runs exactly once
Recursive term	joins the CTE to itself, re-runs each round on the prior round's output only

Together

sql

WITH RECURSIVE subordinates AS (
    SELECT id, manager_id FROM employees WHERE id = 1        -- base case
    UNION ALL
    SELECT e.id, e.manager_id FROM employees e
    JOIN subordinates s ON e.manager_id = s.id                -- recursive term
)
SELECT * FROM subordinates;

Remember: A recursive CTE runs its base case once, then repeatedly joins the recursive term against only the previous round's output — it terminates automatically on a hierarchy, but a cyclic graph needs an explicit visited-node guard.

When a CTE Improves Clarity vs Complicates Optimization

standardintermediate

A CTE names a piece of logic, which helps readability once a query has several steps. On PostgreSQL 12+, a non-recursive CTE referenced once can usually be inlined by the planner just like a subquery — but a CTE referenced multiple times, or one the planner chooses not to inline, is optimized as its own unit, which can be better or worse depending on the query.

Think of it as

Before PostgreSQL 12, a CTE was an "optimization fence" — the planner always computed it in isolation and never pushed outer WHERE conditions into it, which could make a CTE with a large intermediate result far slower than the equivalent subquery. From PostgreSQL 12 onward, the planner treats a non-recursive CTE referenced exactly once as inlinable by default, closing most of that gap — but a CTE referenced multiple times is still typically materialized once and reused, which is the actual remaining reason to reach for one over a repeated subquery: computing an expensive result once instead of N times.

sql

-- default: planner decides whether to inline
WITH recent AS (SELECT * FROM orders WHERE created_at > now() - interval '7 days')
SELECT * FROM recent WHERE customer_id = 42;

-- force the old fence behavior, e.g. to compute once deliberately
WITH recent AS MATERIALIZED (SELECT * FROM orders WHERE created_at > now() - interval '7 days')
SELECT * FROM recent WHERE customer_id = 42;

What we're doing: Compare EXPLAIN on a single-reference CTE against the same logic marked MATERIALIZED, to see the plan difference directly.

cte_inlining.sqlsql

EXPLAIN
WITH recent AS (SELECT * FROM orders WHERE created_at > now() - interval '7 days')
SELECT * FROM recent WHERE customer_id = 42;
-- default: planner may inline "recent" and push customer_id = 42 down into it

EXPLAIN
WITH recent AS MATERIALIZED (SELECT * FROM orders WHERE created_at > now() - interval '7 days')
SELECT * FROM recent WHERE customer_id = 42;
-- forced: "recent" computes its full 7-day window first, THEN filters by customer_id

2: Default (no MATERIALIZED keyword): PostgreSQL 12+ is free to inline this single-reference CTE.
3: If inlined, customer_id = 42 can be evaluated as part of scanning orders, potentially using an index on customer_id.
7: MATERIALIZED forces the pre-12 behavior: the full 7-day window is computed first, in isolation, before customer_id = 42 is applied.

Output

QUERY PLAN
-----------------------------------------------------
Index Scan using orders_customer_id_idx on orders
  Index Cond: (customer_id = 42)
  Filter: (created_at > (now() - '7 days'::interval))

QUERY PLAN
-----------------------------------------------------
CTE Scan on recent
  Filter: (customer_id = 42)
  CTE recent
    ->  Seq Scan on orders
          Filter: (created_at > (now() - '7 days'::interval))

Why this works: When inlined, the planner treats customer_id = 42 and the date filter as one combined condition on orders, free to use whichever index helps most — here, an index on customer_id, scanning only that customer's rows. When MATERIALIZED, PostgreSQL computes "every order from the last 7 days" as a complete, isolated intermediate result first via a full sequential scan, and only then filters that intermediate result down to customer 42 — doing far more work if most of the 7-day window belongs to other customers.

Assuming a CTE is always either "just a named subquery" or always "an optimization fence"

Wrong

sql

-- assuming this is always fenced (pre-12 mental model), when it may be inlined
WITH big AS (SELECT * FROM huge_table)
SELECT * FROM big WHERE id = 42;

Better

sql

-- be explicit about intent when it matters
WITH big AS NOT MATERIALIZED (SELECT * FROM huge_table)
SELECT * FROM big WHERE id = 42;
-- or check EXPLAIN rather than assuming either behavior

What you see: A query written expecting the old "CTE always fences" behavior performs surprisingly well (or a query written expecting always-inlined behavior performs surprisingly poorly), because the actual default depends on the PostgreSQL version and the exact reference count.

Why: The inlining default changed in PostgreSQL 12, so guidance written against an older version, or intuition carried over from one, no longer describes current behavior — and even on 12+, a CTE referenced more than once is not inlined the same way a single-reference one is. Checking EXPLAIN directly, or using the explicit MATERIALIZED/NOT MATERIALIZED keyword when the choice actually matters, removes the guesswork entirely rather than relying on a version-dependent default.

When a CTE's optimization behavior differs from an inline subquery

When a CTE's optimization behavior differs from an inline subquery
Situation	PostgreSQL 12+ default behavior
Non-recursive CTE, referenced once	inlined — same plan as an equivalent subquery
Non-recursive CTE, referenced multiple times	usually computed once, reused — can beat repeating the subquery
Recursive CTE	always its own execution unit — never inlined
CTE marked MATERIALIZED	forced to compute in isolation, ignoring outer conditions

Together

sql

WITH recent AS NOT MATERIALIZED (SELECT * FROM orders WHERE created_at > now() - interval '7 days')
SELECT * FROM recent WHERE customer_id = 42;
-- NOT MATERIALIZED lets the planner push customer_id = 42 into the CTE

Remember: On PostgreSQL 12+, a single-reference non-recursive CTE is usually inlined like a subquery by default; a multi-reference CTE is usually computed once and reused — use MATERIALIZED/NOT MATERIALIZED explicitly when the default choice matters, and check EXPLAIN rather than assuming.