CTEs: Common Table Expressions

Concept. A CTE (WITH name AS (...)) names an intermediate query result so the rest of the query can reference it like a table.

Intuition. Find the users who listen to Rock by naming the steps: a RockSongs CTE lists the Rock song ids, a RockListeners CTE finds who played them, then the final query reads their names. Each step is a named table you reference, instead of nesting one subquery inside another.

Goal: Identify users who listen to Rock genre songs.

A before-and-after. The left panel (red, the problem) shows three subqueries nested inside one another, read inside-out and hard to debug. The right panel (green, the better way) shows the same logic as three named CTE steps read top to bottom: RockSongs returns {3,4,5,9}, RockListeners returns {1,2,3}, the final query returns {Mickey, Minnie, Daffy}. Color key: red is the hard-to-read nested way, green is the clear CTE way, grey is a neutral step label, and the ink target marks the takeaway.

Figure 1. A CTE turns nested chaos into named, linear steps. Left (red): subqueries nested inside one another, read inside-out and hard to debug. Right (green): the same logic as named steps read top to bottom. Same result, far easier to follow.

Why CTEs?

Three reasons databases made CTEs first-class. One, readable: each step is a named block read top to bottom. Two, optimizer-friendly: the engine can reason about and optimize each named block on its own. Three, recursion: a CTE can reference itself to walk org charts, trees, and dependency graphs.

  • Comparable to functions in Python/Java - Named, reusable computations defined before the outer SELECT that uses them.

  • Linear to read - top to bottom, like steps in a recipe.

  • Debug intermediate steps - test each CTE on its own.

  • Reusable - reference the same CTE multiple times.

  • Recursion support for hierarchical data (a unique feature of CTEs).

Simple CTE: HighRatedSongs

-- Step 1: WITH names a result you can reuse
WITH HighRatedSongs AS (
    SELECT song_id, AVG(rating) AS avg_rating
    FROM Listens
    GROUP BY song_id
    HAVING AVG(rating) > 4.0
)
-- Step 2: the outer query reads it by name
SELECT s.title, h.avg_rating
FROM HighRatedSongs h
JOIN Songs s ON h.song_id = s.song_id

HighRatedSongs CTE

song_idavg_ratingNotes
24.6Song 2: (4.2 + 4.7 + 4.9) / 3 = 4.6 > 4.0
74.6Song 7: Single rating 4.6 > 4.0

Final Output

titleavg_ratingNotes
Willow4.6From HighRatedSongs CTE
Yellow Submarine4.6From HighRatedSongs CTE

  • CTE calculates averages first, then filters.

  • Only songs 2 and 7 pass the >4.0 threshold.

  • Main query joins CTE with Songs for titles.

Multiple CTEs: User Statistics

-- Step 1: count every user's listens
WITH UserListenCounts AS (
    SELECT user_id, COUNT(*) AS listen_count
    FROM Listens
    GROUP BY user_id
),
-- Step 2: average only their 4+ ratings
UserHighRatings AS (
    SELECT user_id, AVG(rating) AS high_avg, COUNT(*) AS high_count
    FROM Listens
    WHERE rating >= 4.0
    GROUP BY user_id
)
-- Step 3: join both CTEs back to Users
SELECT u.name, lc.listen_count, hr.high_avg, hr.high_count
FROM Users u
LEFT JOIN UserListenCounts lc ON u.user_id = lc.user_id
LEFT JOIN UserHighRatings hr ON u.user_id = hr.user_id

Note: These could be combined using CASE statements (e.g., COUNT(CASE WHEN rating >= 4.0 THEN 1 END)), but separating them is clearer: each CTE has one purpose, filtering logic is obvious, and it demonstrates how multiple CTEs work together.

UserListenCounts CTE

user_idlisten_countNotes
13Mickey has 3 listens
23Minnie has 3 listens
33Daffy has 3 listens

UserHighRatings CTE

user_idhigh_avghigh_countNotes
14.352Mickey: (4.5 + 4.2) / 2
24.652Minnie: (4.7 + 4.6) / 2
34.91Daffy: Only 4.9 >= 4.0

Final Output

namelisten_counthigh_avghigh_count
Mickey34.352
Minnie34.652
Daffy34.91
PlutoNULLNULLNULL

  • Two CTEs with different purposes: all listens vs high ratings only.

  • UserHighRatings filters to ratings >= 4.0 before aggregating.

  • Main query combines both CTEs with LEFT JOINs.

  • Pluto gets NULL values (no listens to count).

Common Patterns

Three CTE patterns and performance notes. A reusable named step: a CTE names a result you reference several times in one query, instead of repeating the subquery. Index the join keys: a CTE result joins back on source columns, so index those base-table columns. Materialized or inlined: whether the engine computes the CTE once (MATERIALIZED, a reused temp result) or inlines it into the query and may re-run it depends on the database.

  • A reusable named step. A CTE names a result you can reference several times in one query, instead of repeating the same subquery.

  • Index the join keys. When a CTE result joins back on source columns, index those columns in the base tables to keep the join fast.

  • Materialized or inlined. Whether the engine computes a CTE once (materialized into a reused temp result) or folds it into the query (inlined, and possibly re-run) depends on the database. Older engines and WITH ... AS MATERIALIZED force compute-once-and-reuse, which helps when a CTE is expensive and referenced several times; modern defaults (Postgres 12+) often inline it instead, which lets the optimizer push your WHERE filters inside.