Common table expressions (WITH)

CTEs turn nested, unreadable subqueries into a top-to-bottom pipeline a reviewer can follow.

A common table expression (CTE) names a subquery up front with WITH, so the main query can use it like a table:

WITH spend AS (
    SELECT customer_id, SUM(total) AS spent
    FROM orders WHERE status <> 'cancelled'
    GROUP BY customer_id
)
SELECT c.name, s.spent
FROM spend s JOIN customers c ON c.id = s.customer_id;

Several CTEs can be chained, separated by commas, and each may refer to the ones before it. A query becomes a readable pipeline: compute per-customer spend, then rank it, then keep the top rows. A CTE can also be referenced more than once in the main query, which a derived table cannot.

Performance used to be the catch. Before PostgreSQL 12, every CTE was an optimisation fence: it was computed in full and stored, and filters from the outer query were not pushed into it. Since version 12, a side-effect-free CTE referenced once is inlined like a subquery. You can choose explicitly: WITH x AS MATERIALIZED (...) forces one evaluation (useful when the CTE is expensive and used several times), and AS NOT MATERIALIZED forces inlining. Other databases make their own choices; SQL Server always inlines, for instance.

PostgreSQL also allows data-modifying CTEs: INSERT, UPDATE or DELETE ... RETURNING inside WITH. That moves rows between tables in a single statement, atomically:

WITH moved AS (
    DELETE FROM orders WHERE status = 'cancelled' RETURNING *
)
INSERT INTO orders_archive SELECT * FROM moved;

All parts of such a statement see the same snapshot of the data, so one CTE cannot see rows another CTE in the same statement just modified; pass them through RETURNING instead.

Example

WITH spend AS (
    SELECT customer_id, SUM(total) AS spent
    FROM orders
    WHERE status <> 'cancelled'
    GROUP BY customer_id
),
average AS (
    SELECT AVG(spent) AS avg_spent FROM spend
)
SELECT c.name, s.spent
FROM spend s
JOIN customers c ON c.id = s.customer_id
CROSS JOIN average a
WHERE s.spent > a.avg_spent;          -- avg of 257.50 and 247.99 = 252.745
--  name | spent
--  Ana  | 257.50

-- Force a single evaluation of an expensive CTE used twice
WITH heavy AS MATERIALIZED (
    SELECT product_id, SUM(quantity) AS sold FROM order_items GROUP BY product_id
)
SELECT (SELECT MAX(sold) FROM heavy) AS best, (SELECT MIN(sold) FROM heavy) AS worst;
--  best | worst
--     3 |     1

Remember

WITH names intermediate results so a query reads top to bottom
Since PostgreSQL 12, single-use CTEs are inlined; MATERIALIZED / NOT MATERIALIZED override
Data-modifying CTEs with RETURNING move data atomically in one statement

Watch out for

Assuming a CTE is always computed once and cached; in modern PostgreSQL it may be inlined and re-planned.

Check yourself

In PostgreSQL 12+, what happens to a side-effect-free CTE referenced once in the main query?
What is a reason to use a CTE instead of a derived table in FROM?

Official docs: www.postgresql.org/docs/current/queries-with.html

Example

Remember

Watch out for

Check yourself

Builds on

Unlocks