Common table expressions

with: the same subquery, given a name

A common table expression is a subquery with a name, declared before the query that uses it. Nothing changes about what runs — everything changes about who can read it in six months. Nested subqueries are read inside out; a chain of CTEs is read downwards, like a paragraph.

with order_totals as (
  select order_id, sum(qty * unit_price) as total
  from   order_items
  group by order_id
)
select round(avg(total), 2) as average_order
from   order_totals;

Several steps, each one named

Each CTE may use the ones declared before it. A single-row CTE like average is a neat way to compute a threshold once and compare against it without repeating the whole subquery.

with monthly as (
  select   substr(o.placed, 1, 7)             as month,
           sum(i.qty * i.unit_price)          as revenue
  from     orders o
  join     order_items i on i.order_id = o.id
  where    o.status <> 'cancelled'
  group by 1
),
average as (
  select avg(revenue) as mean from monthly
)
select   m.month, round(m.revenue, 2) as revenue
from     monthly m, average a
where    m.revenue > a.mean
order by m.month;

A CTE is not always a fence

In PostgreSQL before version 12, a CTE was an optimisation barrier: it was always materialised, sometimes ruining a query. Since 12 it is inlined when it is used once, and materialized / not materialized let you say. SQLite and MySQL 8 inline where they can.

The practical rule: write CTEs for clarity, and if one is enormous and used once inside a huge query, check the plan before assuming it is free.

with recursive: the query that calls itself

The anchor produces the first rows. The step runs against whatever the last round produced, and keeps going until it produces nothing. union all keeps duplicates and is the usual choice; union de-duplicates and can stop a cycle from running forever.

with recursive n(x) as (
  select 1                       -- the anchor: where it starts
  union all
  select x + 1 from n where x < 10   -- the step, which reads the rows so far
)
select x from n;                 -- 1 … 10

What recursion is actually for

Generating a complete calendar and left joining the data onto it is the standard fix for "the chart skips the quiet months". The other classic use is a hierarchy: start at a node, join the children, repeat — an organisation chart, a category tree, a thread of replies.

with recursive months(month) as (
  select '2024-01'
  union all
  select printf('%04d-%02d', 2024, cast(substr(month, 6, 2) as integer) + 1)
  from   months
  where  month < '2024-12'
)
select   m.month,
         coalesce(round(sum(i.qty * i.unit_price), 2), 0) as revenue
from     months m
left join orders o      on substr(o.placed, 1, 7) = m.month and o.status <> 'cancelled'
left join order_items i on i.order_id = o.id
group by m.month
order by m.month;