BrevFeed
Sign in

27. Common table expressions: WITH

📖 Reading · 6 min
💡 Every code box below is live — edit it and hit Run.

Naming a subquery, up front

A CTE (Common Table Expression), written with WITH name AS (...), defines a named, temporary result set before the main query, which can then reference it by name like a real table — as many times as needed:

WITH rating_summary AS (
    SELECT rating, avg(rental_rate) AS avg_rate
    FROM film
    GROUP BY rating
)
SELECT rating, avg_rate
FROM rating_summary
WHERE avg_rate > 3.00
ORDER BY avg_rate DESC;

This is the exact same query as the derived-table version from the previous lesson — same result, same (in modern Postgres) execution strategy. The difference is purely about how you write and read it.

Why CTEs win on readability

Compare the previous lesson's "each customer's spend vs. the overall average" query, rewritten with CTEs:

WITH customer_spend AS (
    SELECT customer_id, sum(amount) AS total
    FROM payment
    GROUP BY customer_id
),
overall_avg AS (
    SELECT avg(total) AS avg_total FROM customer_spend
)
SELECT c.customer_id, c.first_name, cs.total, oa.avg_total
FROM customer c
JOIN customer_spend cs ON cs.customer_id = c.customer_id
CROSS JOIN overall_avg oa
WHERE cs.total > oa.avg_total
ORDER BY cs.total DESC
LIMIT 10;

Notice overall_avg references customer_spend by name — no re-nesting the whole aggregation a second time, the way the pure-derived- table version had to repeat the sum(amount) ... GROUP BY subquery verbatim inside a nested FROM. This is the core readability win: each step gets a name, later steps reference earlier steps directly, and the whole pipeline reads top-to-bottom instead of inside-out. Multiple CTEs, comma-separated, later ones can reference earlier ones — this is what makes multi-step logic tractable to both write and review.

CTEs vs. derived tables — not (usually) a performance choice

A common misconception, worth correcting directly: in Postgres 12+, a CTE is not automatically materialized (computed once and cached) — the planner is free to inline it into the surrounding query, same as a derived table, if that's cheaper. (Pre-12 Postgres always materialized every CTE, which used to make this a real performance decision — Module 12 covers the current materialization rules, including how to force either behavior explicitly with MATERIALIZED/NOT MATERIALIZED.) For this course's purposes: choose CTEs over derived tables for readability, not because you expect a performance difference — on modern Postgres, the planner usually produces the same plan either way.

CTEs referencing multiple times — one computation, reused by name

WITH film_revenue AS (
    SELECT f.film_id, f.title, sum(p.amount) AS revenue
    FROM film f
    JOIN inventory i ON i.film_id = f.film_id
    JOIN rental r ON r.inventory_id = i.inventory_id
    JOIN payment p ON p.rental_id = r.rental_id
    GROUP BY f.film_id, f.title
)
SELECT
    (SELECT title FROM film_revenue ORDER BY revenue DESC LIMIT 1) AS top_earner,
    (SELECT title FROM film_revenue ORDER BY revenue ASC LIMIT 1) AS lowest_earner,
    (SELECT avg(revenue) FROM film_revenue) AS avg_revenue;

film_revenue is computed once (conceptually — the planner still decides the actual execution strategy) and referenced three times here, each with a different follow-up operation. Rewriting this without a CTE would mean either repeating the entire five-table join three times, or a much more awkward single-query construction — the CTE is what makes "compute this once, use it several ways" a natural way to write the query at all.

A word on CTEs as a mental scaffolding tool

Beyond the concrete cases above, CTEs are worth reaching for whenever a query has more than two logical steps, purely for the sake of writing correct SQL in the first place — naming each intermediate result forces you to be precise about what each step actually produces, which is often where a bug would otherwise hide inside a deeply nested subquery. This is a stylistic recommendation this course leans on for anything non-trivial from here on.

Check yourself

  1. What's the main difference in intent between reaching for a CTE and reaching for a derived table, given that modern Postgres often produces the identical execution plan for both?
  2. Can a later CTE in a WITH block reference an earlier one? Can an earlier one reference a later one?
  3. Rewrite the film_revenue example so a fourth line computes "how many distinct films earned above the average revenue," still referencing film_revenue only once in the WITH clause.
← Previous Next →