Common Table Expressions (CTE)

A Common Table Expression, or CTE, lets you name a subquery with a WITH clause and then reference that name later in the same statement — as if it were a temporary, throwaway table that exists only for the duration of the query.

Basic syntax

SQL

WITH name AS (
  SELECT ...
)
SELECT * FROM name WHERE ...;

Why CTEs beat deeply nested subqueries

Once a query needs two or three levels of subquery nesting, it becomes hard to read from the inside out. CTEs let you flatten that nesting into a sequence of named, top-to-bottom steps — closer to how you'd actually explain the query out loud.

Before — nested subqueries

SQL

SELECT region, avg_order_value
FROM (
  SELECT region, AVG(total) AS avg_order_value
  FROM orders
  WHERE status = 'delivered'
  GROUP BY region
) AS region_avg
WHERE avg_order_value > (
  SELECT AVG(total) FROM orders WHERE status = 'delivered'
);

After — same query using CTEs

SQL

WITH delivered_orders AS (
  SELECT * FROM orders WHERE status = 'delivered'
),
region_avg AS (
  SELECT region, AVG(total) AS avg_order_value
  FROM delivered_orders
  GROUP BY region
),
overall_avg AS (
  SELECT AVG(total) AS avg_order_value FROM delivered_orders
)
SELECT region_avg.region, region_avg.avg_order_value
FROM region_avg, overall_avg
WHERE region_avg.avg_order_value > overall_avg.avg_order_value;

Each named step is small, self-contained, and readable on its own — and because delivered_orders is defined once and referenced twice, the intent ("filter first, then compute two different averages from the same filtered set") is much clearer than reading the nested version inside-out.

Multiple CTEs in one WITH clause

As shown above, a single WITH can define several CTEs, separated by commas. Later CTEs can reference earlier ones defined in the same WITH clause.

PostgreSQL 12+: CTEs are no longer always an optimization fence

This is a genuinely important, version-specific detail. Before PostgreSQL 12, every CTE was materialized — computed in full and stored in a temporary work area — before the outer query could use it. This was called an "optimization fence": it prevented the planner from pushing filters or reordering work across the CTE boundary, which sometimes made CTEs surprisingly slow compared to writing the equivalent subquery inline.

Starting with PostgreSQL 12, the planner is allowed to inline a CTE — treat it like a plain subquery and optimize across its boundary — as long as it's referenced only once and doesn't contain something that requires materialization (like FOR UPDATE, or being referenced by a recursive query). This generally makes CTEs perform the same as an equivalent subquery, removing the old reason to avoid them for performance reasons.

Force materialization when you want the old fence behavior

SQL

WITH expensive_calc AS MATERIALIZED (
  SELECT customer_id, SUM(total) AS lifetime_value
  FROM orders
  GROUP BY customer_id
)
SELECT * FROM expensive_calc WHERE lifetime_value > 1000;

Use AS MATERIALIZED when you deliberately want the CTE computed once and reused as-is — for example, if it's referenced many times and recomputing it per reference would be wasteful, or if inlining would let the planner make a bad choice for an expensive computation. Use AS NOT MATERIALIZED to explicitly request inlining even in cases where PostgreSQL might otherwise materialize.

Note

If you're running PostgreSQL 11 or earlier, every CTE still behaves as an optimization fence — there is no MATERIALIZED keyword to opt out, because materializing was the only behavior available.

Tip

Need a CTE that refers to itself, for hierarchical or graph-like data such as an org chart or a bill of materials? That's WITH RECURSIVE, covered on the next page.

WITH name AS (...) gives a subquery a name you can reference later in the same statement.
CTEs flatten deeply nested subqueries into readable, named, sequential steps.
PostgreSQL 12+ can inline a singly-referenced CTE into the outer query and optimize across it, removing the old always-materializes behavior.
Use AS MATERIALIZED to force the pre-12 fence behavior when you need it; AS NOT MATERIALIZED to force inlining.