beginnerintermediateadvanced

PostgreSQL · Subqueries and CTEs

Subqueries and CTEs

Scalar vs correlated subqueries, why NOT EXISTS beats NOT IN when NULLs are possible, and naming logic with WITH — including the recursive form

Correlatedreferences an outer column — re-evaluated per row
NOT IN + NULL = 0 rowssilently, no error — prefer NOT EXISTS
WITH names a subqueryscoped to one statement, not persistent
WITH RECURSIVEbase case + recursive term, stops when nothing new appears

1.Scalar, Correlated and Non-Correlated Subqueries

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.

Key features

Scalar means "returns exactly one column, one row" — usable anywhere a single value is expected
Non-correlated subqueries can run once and be reused; correlated ones reference an outer column
A correlated subquery in WHERE is conceptually re-evaluated per outer row, though the planner may optimize the physical execution
Using a multi-row subquery where a scalar is expected raises "more than one row returned by a subquery used as an expression"
EXISTS/NOT EXISTS are typically correlated and do not require the subquery to be scalar

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

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)

Command

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;

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.

2.Using EXISTS and NOT EXISTS Appropriately

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.

Key features

EXISTS returns true the instant the subquery finds one matching row — it does not need to scan every possible match
The subquery inside EXISTS conventionally selects a constant like 1, since the actual column values are never used
EXISTS is almost always correlated — an EXISTS with no reference to the outer query is rarely meaningful
NOT EXISTS is the standard way to find rows in one table with no matching row in another
Unlike NOT IN, NOT EXISTS is unaffected by NULLs in the subquery's result

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

Command

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);

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.

3.IN vs EXISTS and NULL-Related Edge Cases

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."

Key features

x IN (a, b, NULL) can be true, but x NOT IN (a, b, NULL) can never be true — it evaluates to NULL, not false
This means NOT IN with a subquery containing any NULL returns zero rows, silently, with no error
NOT EXISTS is unaffected because it never compares a value against the subquery's results — it only checks row existence
IN (without NOT) is unaffected in practice, since one matching non-NULL value is enough to make it true
The safe default for "find rows with no match" is NOT EXISTS, not NOT IN

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

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

Command

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);

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."

4.Common Table Expressions Using WITH

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.

Key features

A CTE is scoped to the single statement it appears in — it does not persist like a view or table
A CTE can be referenced multiple times in the main query without repeating its logic
Multiple CTEs can be chained in one WITH clause, each able to reference earlier ones
PostgreSQL 12+ can inline a non-recursive CTE into the main query when beneficial, unlike older versions' hard optimization fence
A CTE with a side-effecting statement (INSERT/UPDATE/DELETE ... RETURNING) is a "data-modifying CTE," a distinct and more advanced use

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

A CTE feeding the main query

referencedlike a table

WITH big_orders AS (...)

named result set

Main query

references big_orders by name

Result

CTE exists only for this statement

Command

sql

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

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

5.Recursive CTEs and When They Are Appropriate

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.

Key features

WITH RECURSIVE has a base case, UNION or UNION ALL, and a recursive term referencing the CTE by its own name
The recursive term must reference the CTE exactly once, and only in its FROM clause
Recursion stops automatically once a round produces zero new rows
UNION ALL is typically used over UNION for performance; use UNION only if duplicate elimination between rounds is needed
A recursive CTE with a cyclic graph (not a strict hierarchy) needs an explicit cycle guard or it never terminates

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

Command

sql

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

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.

6.When a CTE Improves Clarity vs Complicates Optimization

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.

Key features

PostgreSQL 12+ inlines a non-recursive CTE referenced once by default, same as an ordinary subquery
A CTE referenced multiple times in the main query is typically computed once and reused across all references
MATERIALIZED / NOT MATERIALIZED can force the old fence behavior or force inlining explicitly, when the default choice is wrong
A recursive CTE can never be inlined — it is always its own execution unit
Naming intermediate steps with CTEs is a genuine readability win regardless of version, independent of the optimization question

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

Command

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;

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.

Spotted a mistake on this page? Tell us.

Read Sheet