WITH (common table expressions)

Names one or more intermediate results at the top of a query so it reads top-down in steps.

Syntax

WITH step1 AS (
  SELECT ...
), step2 AS (
  SELECT ... FROM step1 ...
)
SELECT ... FROM step2 ...;

WITH x AS [NOT] MATERIALIZED (...)   -- planner hint

A CTE is a named subquery that exists only for the one statement. Later CTEs and the main query can refer to earlier ones, so a complex query becomes a pipeline of readable steps.

Each CTE can be referenced several times.
Since PostgreSQL 12, a CTE used once with no side effects is inlined into the main query (optimised like a subquery). One referenced several times is computed once (materialized). MATERIALIZED / NOT MATERIALIZED override this.
CTEs are great for pre-aggregating each one-to-many table separately before joining (avoids fan-out).
A CTE is not a table: it disappears after the statement. Use a view or temporary table to reuse across queries.
Data-modifying statements may appear in WITH (WITH moved AS (DELETE ... RETURNING *) INSERT ...).
WITH RECURSIVE lets a CTE refer to itself (hierarchies, sequences).

Examples

Step-by-step monthly report shop

WITH order_values AS (
  SELECT o.order_id, o.ordered_at, sum(oi.quantity * oi.unit_price - oi.discount_amount) + o.shipping_fee AS order_value
  FROM orders o
  JOIN order_items oi USING (order_id)
  WHERE o.status = 'delivered'
  GROUP BY o.order_id
), monthly AS (
  SELECT date_trunc('month', ordered_at)::date AS month, count(*) AS orders, sum(order_value) AS revenue
  FROM order_values
  GROUP BY month
)
SELECT month, orders, round(revenue, 2) AS revenue, round(revenue / orders, 2) AS avg_order_value
FROM monthly
WHERE month >= '2025-01-01'
ORDER BY month;

Grouping by o.order_id (the primary key) lets the CTE select o.ordered_at and o.shipping_fee.

Reusing a CTE twice starter

WITH per_customer AS (
  SELECT customer_id, sum(quantity) AS units
  FROM orders
  GROUP BY customer_id
)
SELECT pc.customer_id, pc.units
FROM per_customer pc
WHERE pc.units > (SELECT avg(units) FROM per_customer)
ORDER BY pc.units DESC, pc.customer_id;

The same intermediate result feeds both the outer query and the comparison subquery.

Pitfalls

Ending a CTE list with a comma before the main SELECT (syntax error).
Forgetting the main SELECT: a WITH clause alone is not a statement.
Expecting a CTE to persist for the next query.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

MySQL: CTEs since 8.0.
SQL Server: The previous statement must end with ; before WITH. CTEs are always inlined (no MATERIALIZED).