Naming Your Steps: Common Table Expressions

Series 1 got you fluent in the clauses: select, filter, join, group, subquery. This series is about the queries you write once the questions stop being simple — the ones that arrive as several steps stacked on top of each other. “Rank the books within each genre.” “Show me each month next to the one before it.” “Give me the running total.” None of those fit in a single flat SELECT, and the tools that make them fit are the subject of the whole series: common table expressions, then window functions, then grouping sets.

We start with the one that changes how a query reads. A common table expression, written with WITH, lets you name a subquery and then use that name like a table. It sounds cosmetic. It isn’t. It’s the difference between a query you read inside-out and one you read top-to-bottom.

Naming a subquery

Here’s a two-step question: which months in 2025 had more than 1,850 orders? Step one is to count orders per month. Step two is to keep the busy months. In Series 1 you’d have nested step one inside a subquery in the FROM clause. A CTE pulls it out and gives it a name:

WITH monthly AS (
  SELECT date_trunc('month', order_date)::date AS month,
         count(*) AS orders
  FROM orders
  WHERE order_date >= '2025-01-01'
  GROUP BY 1
)
SELECT month, orders
FROM monthly
WHERE orders > 1850
ORDER BY month;

   month    | orders
------------+--------
 2025-01-01 |   1882
 2025-03-01 |   1855
 2025-04-01 |   1865
 2025-05-01 |   1862
 2025-08-01 |   1927
(5 rows)

Read it downward. WITH monthly AS (...) defines a temporary named result — the per-month counts — and the final SELECT treats monthly as if it were a real table, filtering it with an ordinary WHERE. Notice that the filter orders > 1850 refers to orders, the alias from inside the CTE. That’s the payoff: once a subquery has a name and named columns, the outer query talks to it in plain terms, with no aggregate re-stated. date_trunc('month', ...) collapses each date to the first of its month, and ::date trims off the time part Postgres would otherwise print.

The scope of a CTE is the single statement it’s attached to. monthly exists for the length of this query and is gone the moment it finishes. It is not a table you created; it’s a name that lives and dies with one statement.

Chaining CTEs, and referring back

One WITH can define several CTEs, separated by commas, and — this is the part that makes them powerful — a later CTE can reference an earlier one. That turns a query into a readable pipeline: each step names its input, does one thing, and hands a named result to the next.

Say you want each genre’s revenue and its share of the whole shop. That’s naturally two steps: total the revenue per genre, then compare each to the grand total. Written as one flat query you’d compute the grand total as a scalar subquery buried in the SELECT list. As a chain it reads like the sentence you’d say out loud:

WITH genre_rev AS (
  SELECT b.genre,
         round(sum(oi.quantity * oi.unit_price), 2) AS revenue
  FROM order_items oi
  JOIN books b ON b.book_id = oi.book_id
  GROUP BY b.genre
),
grand AS (
  SELECT sum(revenue) AS total FROM genre_rev
)
SELECT gr.genre,
       gr.revenue,
       round(100.0 * gr.revenue / g.total, 1) AS pct_of_total
FROM genre_rev gr
CROSS JOIN grand g
ORDER BY gr.revenue DESC;

   genre    |  revenue   | pct_of_total
------------+------------+--------------
 Sci-Fi     | 1567345.25 |         17.4
 History    | 1543230.51 |         17.1
 Children   | 1525966.72 |         16.9
 Nonfiction | 1504489.80 |         16.7
 Poetry     | 1485716.37 |         16.5
 Fiction    | 1390433.23 |         15.4
(6 rows)

Three named stages. genre_rev totals revenue per genre off the joined line items. grand reads from genre_rev — not from the base tables — and sums the six numbers into one. The final query joins the two: because grand is a single row, a CROSS JOIN staples that grand total onto every genre row, and the arithmetic gives each genre’s percentage. The 100.0 (not 100) keeps the division fractional, dodging the integer-division trap from Series 1.

You could write this without CTEs. grand could be (SELECT sum(...) FROM ...) inlined twice, or a window function, which is where we’re headed in two chapters. But the chained form is the one you can hand to someone else and have them understand in one read. Each CTE is a paragraph; the query is an essay with a beginning, middle, and end. When a query is doing real work, that readability is not a luxury.

The inlining rule: fences, and Postgres 12

Here’s a piece of Postgres history worth carrying, because the old advice is still repeated everywhere and it’s now wrong.

Before Postgres 12, a CTE was an optimization fence. Whatever was inside the WITH ran to completion and materialized into a temporary result, and only then did the outer query use it. The planner was forbidden from pushing the outer query’s filters down into the CTE. People exploited this deliberately to force a materialization, and people got bitten by it accidentally when a filter that would have used an index got stranded outside the fence.

Since Postgres 12, a CTE that’s referenced once and has no side effects is inlined by default. It’s folded into the outer query and optimized as a whole, exactly like a subquery. You can watch it happen. Here’s a CTE referenced once, with EXPLAIN showing the plan:

EXPLAIN
WITH cheap_books AS (
  SELECT book_id, title, price FROM books WHERE price < 10
)
SELECT * FROM cheap_books WHERE title LIKE 'Book Title 1%';

                                QUERY PLAN
--------------------------------------------------------------------------
 Seq Scan on books  (cost=0.00..15.00 rows=10 width=24)
   Filter: ((price < '10'::numeric) AND (title ~~ 'Book Title 1%'::text))
(2 rows)

There is no CTE in that plan at all. The planner dissolved cheap_books and merged both conditions — the inner price < 10 and the outer title LIKE ... — into a single scan with one combined filter. The fence is gone.

Add the keyword MATERIALIZED and you get the old behavior back on purpose:

EXPLAIN
WITH cheap_books AS MATERIALIZED (
  SELECT book_id, title, price FROM books WHERE price < 10
)
SELECT * FROM cheap_books WHERE title LIKE 'Book Title 1%';

                           QUERY PLAN
----------------------------------------------------------------
 CTE Scan on cheap_books  (cost=13.50..14.71 rows=10 width=50)
   Filter: (title ~~ 'Book Title 1%'::text)
   CTE cheap_books
     ->  Seq Scan on books  (cost=0.00..13.50 rows=54 width=24)
           Filter: (price < '10'::numeric)
(5 rows)

Now the CTE is its own node. The inner scan runs first, filtering price < 10 down to an estimated 54 rows, and only then does the outer title filter apply on top. That’s the fence, restored by request. NOT MATERIALIZED forces the opposite. Most of the time you want neither keyword, and the default is right. But knowing the fence exists — and that it’s now opt-in, not automatic — can turn a mysteriously slow query into a one-word fix. (A CTE referenced more than once is still materialized by default, because running it twice would usually be worse.)

MySQL 8 and SQLite both support WITH, and both may or may not inline depending on version and shape; the MATERIALIZED hint is a Postgres extension. If you’re portable, don’t rely on a specific materialization choice.

Final thoughts

A common table expression is a name for a step. Attach WITH name AS (...) to a statement and the rest of the query — including later CTEs — can use that name like a table. One CTE untangles a nested subquery; several chained together turn a gnarly query into a top-to-bottom pipeline you can actually read. And the one performance fact to keep: since Postgres 12 a single-use CTE is inlined, not fenced. It costs nothing over the equivalent subquery unless you write MATERIALIZED to ask for the fence back. That readability, for free, is why CTEs show up in almost every query for the rest of this series. Next we give a CTE the one power an ordinary subquery can never have — the ability to refer to itself.

Next: A query that calls itself: recursive CTEs