Common table expressions (CTEs)

A CTE (Common Table Expression) is a named temporary result set defined with the WITH keyword. Think of it as giving a subquery a name so you can reference it like a table. The result: dramatically more readable multi-step queries.

In PostgreSQL 12+, non-recursive CTEs are inlined by the optimizer, so they perform identically to subqueries. The benefit is purely readability: named steps are easier to write, debug, and maintain than deeply nested subqueries.

subquery_vs_cte.sql
sql
-- Nested subquery: hard to read
SELECT * FROM (
  SELECT seller_id, SUM(price) AS revenue
  FROM order_items
  GROUP BY seller_id
) sub
WHERE revenue > (SELECT AVG(revenue) FROM (
  SELECT seller_id, SUM(price) AS revenue
  FROM order_items
  GROUP BY seller_id
) sub2);

-- Same query as a CTE: much clearer
WITH seller_revenue AS (
  SELECT seller_id, SUM(price) AS revenue
  FROM order_items
  GROUP BY seller_id
)
SELECT *
FROM seller_revenue
WHERE revenue > (SELECT AVG(revenue) FROM seller_revenue);

The CTE version defines seller_revenue once and references it twice. No duplicated logic, no deep nesting.

The real power of CTEs comes when you chain multiple together. Each CTE can reference the ones defined before it, creating a clear step-by-step pipeline. The second step below sneaks in RANK() OVER (ORDER BY total_revenue DESC), a window function that numbers rows 1, 2, 3 by revenue. Window functions get their own deep dive later; for now just read revenue_rank as a position number.

multi_cte.sql
sql
-- Multi-CTE pipeline: each CTE builds on the previous
WITH seller_revenue AS (
  SELECT
    seller_id,
    SUM(price) AS total_revenue,
    COUNT(*) AS items_sold
  FROM order_items
  GROUP BY seller_id
),
seller_ranking AS (
  SELECT
    seller_id,
    total_revenue,
    items_sold,
    RANK() OVER (ORDER BY total_revenue DESC) AS revenue_rank
  FROM seller_revenue
)
SELECT *
FROM seller_ranking
WHERE revenue_rank <= 10;

Multiple CTEs separated by commas create a pipeline. Each CTE can reference the ones defined before it.

Cte pipeline flow

In PostgreSQL 12+, non-recursive CTEs are inlined by the optimizer, so they perform the same as subqueries. Before PG12, CTEs were always materialized (computed once, stored in memory). You can force materialization with AS MATERIALIZED if needed, but the default (inlined) is usually best.

Code playground: Customer spending tiers

Loading practice…

Quiz: Quiz

Loading practice…

AI prompt: Subquery vs cte decision tree

Loading practice…

Flashcards: Flashcards

Loading practice…