Common table expressions

WITH for readable steps, inlined or materialized CTEs, recursive queries and data changes in one statement.

Draft. This page is new and has not yet been reviewed by a second person. Every query output on it was produced by a real run on PostgreSQL 18.6 (PGlite 0.5.9). If something looks wrong, report it.

Before thisReading EXPLAIN
On this page
  1. Readable steps
  2. Inlined or materialized
  3. Recursive queries
  4. Data changes in a CTE
  5. In MySQL
  6. Sources

A common table expression (CTE) is a named subquery written up front with WITH. It makes long queries readable as a sequence of steps, it is the only way to write recursive queries in SQL, and it can chain data changes together. Since PostgreSQL 12, it usually costs nothing extra.

Readable steps#

Big spenders in 2024: first total each user's orders, then join the totals to users.

sql
with spend as (
  select user_id, sum(total) as spent, count(*) as orders
  from orders
  where created_at >= '2024-01-01'
  group by user_id
)
select u.email, s.orders, s.spent
from spend s
join users u on u.id = s.user_id
order by s.spent desc
limit 5;
Result 5 rows; real output from PostgreSQL 18.6 (PGlite 0.5.9)
emailordersspent
user1@example.com345209785.27
user2@example.com13183559.48
user3@example.com11663207.10
user4@example.com8656417.82
user5@example.com8152498.57

Inlined or materialized#

A CTE that is referenced once and has no side effects is inlined: the planner treats it as if you had written a subquery, so conditions from the outer query can be pushed into it and indexes can be used.

sql
create index orders_created_at_idx on orders (created_at);
sql
with recent as (
  select * from orders where created_at >= '2024-12-01'
)
select count(*) from recent where status = 'pending';
Plan real output from PostgreSQL 18.6 (PGlite 0.5.9)
Aggregate  (cost=164.71..164.72 rows=1 width=8)
  ->  Index Scan using orders_created_at_idx on orders  (cost=0.29..164.31 rows=160 width=0)
        Index Cond: (created_at >= '2024-12-01 00:00:00+00'::timestamp with time zone)
        Filter: (status = 'pending'::text)

Mark it MATERIALIZED and it is computed once, on its own, into a temporary result; the outer query reads that result with a CTE Scan, and its conditions can no longer reach inside:

sql
with recent as materialized (
  select * from orders where created_at >= '2024-12-01'
)
select count(*) from recent where status = 'pending';
Plan real output from PostgreSQL 18.6 (PGlite 0.5.9)
Aggregate  (cost=244.73..244.74 rows=1 width=8)
  CTE recent
    ->  Index Scan using orders_created_at_idx on orders  (cost=0.29..154.31 rows=4001 width=31)
          Index Cond: (created_at >= '2024-12-01 00:00:00+00'::timestamp with time zone)
  ->  CTE Scan on recent  (cost=0.00..90.02 rows=160 width=0)
        Filter: (status = 'pending'::text)

Materializing is the right call when a CTE is expensive and referenced several times, or when you deliberately want to stop the planner from merging it into the outer query. Before PostgreSQL 12 every CTE was materialized, which is why older advice warns that CTEs are an "optimisation fence".

Recursive queries#

A recursive CTE has a starting query, UNION ALL, and a query that refers to the CTE itself. It repeats the second part until it produces no new rows, which is how trees and graphs are walked in SQL. A small organisation chart:

sql
create table staff (id int primary key, name text not null, manager_id int references staff);
insert into staff values (1, 'Mira', null), (2, 'Theo', 1), (3, 'Lena', 1), (4, 'Omar', 2), (5, 'Ines', 2), (6, 'Kai', 3), (7, 'Zoe', 4);
sql
with recursive chain as (
  select id, name, 0 as depth, name::text as path
  from staff
  where manager_id is null
  union all
  select s.id, s.name, c.depth + 1, c.path || ' > ' || s.name
  from staff s
  join chain c on s.manager_id = c.id
)
select depth, path from chain order by path;
Result 7 rows; real output from PostgreSQL 18.6 (PGlite 0.5.9)
depthpath
0Mira
1Mira > Lena
2Mira > Lena > Kai
1Mira > Theo
2Mira > Theo > Ines
2Mira > Theo > Omar
3Mira > Theo > Omar > Zoe

Two safety rules. Make sure the recursion stops: a cycle in the data (someone managing their own manager) would loop forever, which the CYCLE clause (PostgreSQL 14 and later) or a depth limit prevents. And index the column the recursive step joins on (here manager_id), because the recursive part runs once per level.

Data changes in a CTE#

A CTE can contain INSERT, UPDATE or DELETE with RETURNING, and the rest of the query can use the rows it returns. Moving the first week of events to an archive in one statement:

sql
create table events_archive (like events);
sql
with moved as (
  delete from events where at < '2024-01-08' returning *
), archived as (
  insert into events_archive select * from moved returning 1
)
select count(*) as moved_to_archive from archived;
Result 1 row; real output from PostgreSQL 18.6 (PGlite 0.5.9)
moved_to_archive
1151

The whole statement runs as one unit: either every row is moved, or nothing changes.

In MySQL#

Not run here; from the MySQL reference manual. MySQL 8.0 added CTEs, including recursive ones, and the optimizer can merge a CTE into the outer query or materialize it. CTEs in MySQL can only contain SELECT; data-changing CTEs as above are a PostgreSQL feature.

Sources#

Esc
↑ ↓ to move↵ to open