Common Table Expressions (CTEs)

Chapter 5 — Common Table Expressions (CTEs)

A Common Table Expression — written with the WITH keyword — is a named, temporary result set that exists only for the duration of a single SQL statement. Think of it as giving a subquery a name so you can refer to it clearly, reuse it, and chain multiple steps together without nesting queries five levels deep.

MySQL added full CTE support in version 8.0, including the powerful recursive variant that can walk tree structures and graphs. If you're still on MySQL 5.7 you won't have CTEs — one more reason to upgrade.

Availability: CTEs (WITH clause, including recursive) require MySQL 8.0+. MariaDB added them in 10.2. Check your version: SELECT VERSION();

1. Basic Syntax — WITH … AS

WITH cte_name AS ( ← name you choose SELECT ... ← any valid SELECT FROM ... WHERE ... ) SELECT * FROM cte_name ← reference the CTE here WHERE ... ;

The CTE name is scoped to the statement that defines it. You cannot reference it from other queries or sessions. It disappears the moment the statement finishes.

Why bother? — CTE vs inline subquery

Both queries below produce identical results. The CTE version is dramatically easier to read, especially once queries grow:

-- ❌ Inline subquery — hard to parse, inner query buried
SELECT customer, total_spent
FROM (
    SELECT
        CONCAT(c.first_name, ' ', c.last_name) AS customer,
        SUM(oi.quantity * oi.unit_price)      AS total_spent
    FROM      customers c
    JOIN      orders o      ON o.customer_id  = c.customer_id
    JOIN      order_items oi ON oi.order_id   = o.order_id
    GROUP BY  c.customer_id
) sub
WHERE total_spent > 100
ORDER BY total_spent DESC;

-- ✅ CTE version — logic named up front, main query reads cleanly
WITH customer_spend AS (
    SELECT
        CONCAT(c.first_name, ' ', c.last_name) AS customer,
        SUM(oi.quantity * oi.unit_price)      AS total_spent
    FROM      customers c
    JOIN      orders o      ON o.customer_id  = c.customer_id
    JOIN      order_items oi ON oi.order_id   = o.order_id
    GROUP BY  c.customer_id
)
SELECT customer, total_spent
FROM   customer_spend
WHERE  total_spent > 100
ORDER BY total_spent DESC;

Good CTE names are nouns, not verbs. Name the result set, not what you're doing: customer_spend, top_books, unpaid_orders — not get_customers or filter_orders. The name should describe what the data is.

2. Chaining Multiple CTEs

Multiple CTEs are separated by commas — only one WITH keyword, multiple named blocks. Each CTE can reference any CTE defined before it in the chain.

WITH
  first_cte AS (
      SELECT ...
  ),
  second_cte AS (
      SELECT ... FROM first_cte   -- can reference first_cte
  ),
  third_cte AS (
      SELECT ... FROM second_cte  -- can reference first or second
  )
SELECT * FROM third_cte;

Example — monthly bestsellers with rank

A three-step chain: aggregate sales → rank by month → filter to top 3 per month:

WITH
  -- Step 1: total units sold per book per month
  monthly_sales AS (
      SELECT
          b.book_id,
          b.title,
          DATE_FORMAT(o.order_date, '%Y-%m') AS month,
          SUM(oi.quantity)                     AS units_sold
      FROM      order_items oi
      JOIN      orders o ON o.order_id  = oi.order_id
      JOIN      books  b ON b.book_id   = oi.book_id
      GROUP BY  b.book_id, b.title, month
  ),

  -- Step 2: rank books within each month
  ranked_sales AS (
      SELECT
          month,
          title,
          units_sold,
          RANK() OVER (PARTITION BY month ORDER BY units_sold DESC) AS rnk
      FROM monthly_sales
  )

-- Final: keep only top 3 per month
SELECT month, rnk, title, units_sold
FROM   ranked_sales
WHERE  rnk <= 3
ORDER BY month DESC, rnk;

RANK() is a window function — covered in full in Chapter 6. For now, just know it numbers rows within each partition (month) by the ORDER BY expression. Ranked CTEs like this are a very common pattern you'll write regularly.

Example — two-stage customer segmentation

WITH
  spend_by_customer AS (
      SELECT
          c.customer_id,
          CONCAT(c.first_name, ' ', c.last_name) AS customer,
          COUNT(DISTINCT o.order_id)            AS order_count,
          SUM(oi.quantity * oi.unit_price)      AS total_spent
      FROM      customers c
      JOIN      orders      o  ON o.customer_id = c.customer_id
      JOIN      order_items oi ON oi.order_id   = o.order_id
      GROUP BY  c.customer_id, customer
  ),

  segmented AS (
      SELECT
          customer,
          order_count,
          total_spent,
          CASE
              WHEN total_spent >= 200 THEN 'VIP'
              WHEN total_spent >=  75 THEN 'Regular'
              ELSE                        'Casual'
          END AS segment
      FROM spend_by_customer
  )

SELECT
    segment,
    COUNT(*) AS customers,
    ROUND(AVG(total_spent), 2) AS avg_spend
FROM   segmented
GROUP BY segment
ORDER BY FIELD(segment, 'VIP', 'Regular', 'Casual');

segment	customers	avg_spend
VIP	3	284.50
Regular	8	112.75
Casual	14	38.20

CTEs are not materialised by default in MySQL 8. The optimiser may evaluate each CTE inline (as if it were a subquery) or materialise it into a temp table depending on cost. You can force materialisation with the MATERIALIZE optimiser hint if you want the CTE evaluated once and reused — useful when a CTE is referenced multiple times in the final query.

3. Recursive CTEs

A recursive CTE references itself. It's the standard SQL way to walk tree structures — category hierarchies, org charts, threaded comments, bill-of-materials — without application-side loops or multiple round-trips to the database.

Anchor member

The starting point — a normal SELECT that returns the root row(s). Runs once.

Recursive member

References the CTE by name to join the previous iteration's output back to the table. Runs repeatedly until it returns zero rows.

WITH RECURSIVE cte_name AS ( -- 1. Anchor: starting rows SELECT id, parent_id, name, 1 AS depth FROM tree_table WHERE parent_id IS NULL UNION ALL -- 2. Recursive: join previous result back to the table SELECT t.id, t.parent_id, t.name, c.depth + 1 FROM tree_table t JOIN cte_name c ON t.parent_id = c.id ) SELECT * FROM cte_name ORDER BY depth, name;

UNION ALL not UNION: Recursive CTEs must use UNION ALL (not UNION DISTINCT). Deduplication during recursion is not supported and would prevent the recursion from working correctly.

Setup — add a genre category tree

Add a self-referential genres table to the bookshop to model a category hierarchy:

CREATE TABLE IF NOT EXISTS genres (
    genre_id   INT          PRIMARY KEY AUTO_INCREMENT,
    name       VARCHAR(80)  NOT NULL,
    parent_id  INT          DEFAULT NULL,
    FOREIGN KEY (parent_id) REFERENCES genres(genre_id)
);

INSERT INTO genres (genre_id, name, parent_id) VALUES
  -- Root categories
  (1,  'Fiction',            NULL),
  (2,  'Non-Fiction',        NULL),
  (3,  'Children',           NULL),
  -- Fiction sub-genres
  (4,  'Literary Fiction',   1),
  (5,  'Science Fiction',    1),
  (6,  'Fantasy',            1),
  (7,  'Crime & Thriller',   1),
  -- Non-Fiction sub-genres
  (8,  'History',            2),
  (9,  'Science',            2),
  (10, 'Self-Help',          2),
  -- Science Fiction sub-sub-genres
  (11, 'Space Opera',        5),
  (12, 'Cyberpunk',          5),
  (13, 'Hard SF',            5),
  -- Fantasy sub-sub-genres
  (14, 'Epic Fantasy',       6),
  (15, 'Urban Fantasy',      6);

Walk the entire tree — with indented display

WITH RECURSIVE genre_tree AS (

    -- Anchor: root genres (no parent)
    SELECT
        genre_id,
        name,
        parent_id,
        0                          AS depth,
        CAST(name AS CHAR(500))   AS path       -- breadcrumb path
    FROM  genres
    WHERE parent_id IS NULL

    UNION ALL

    -- Recursive: join children of the current level
    SELECT
        g.genre_id,
        g.name,
        g.parent_id,
        gt.depth + 1,
        CONCAT(gt.path, ' > ', g.name)
    FROM  genres g
    JOIN  genre_tree gt ON g.parent_id = gt.genre_id

)
SELECT
    genre_id,
    CONCAT(REPEAT('  ', depth), name) AS name_indented,
    depth,
    path
FROM   genre_tree
ORDER BY path;

genre_id	name_indented	depth	path
3	Children	0	Children
1	Fiction	0	Fiction
7	Crime & Thriller	1	Fiction > Crime & Thriller
6	Fantasy	1	Fiction > Fantasy
14	Epic Fantasy	2	Fiction > Fantasy > Epic Fantasy
15	Urban Fantasy	2	Fiction > Fantasy > Urban Fantasy
5	Science Fiction	1	Fiction > Science Fiction
13	Hard SF	2	Fiction > Science Fiction > Hard SF
12	Cyberpunk	2	Fiction > Science Fiction > Cyberpunk
11	Space Opera	2	Fiction > Science Fiction > Space Opera
2	Non-Fiction	0	Non-Fiction
8	History	1	Non-Fiction > History
9	Science	1	Non-Fiction > Science
10	Self-Help	1	Non-Fiction > Self-Help

Find all descendants of a single genre

Change the anchor to start at a specific node — the recursion will collect everything below it:

-- All sub-genres under "Fiction" (genre_id = 1)
WITH RECURSIVE subtree AS (

    SELECT genre_id, name, parent_id, 0 AS depth
    FROM   genres
    WHERE  genre_id = 1                    -- ← start here

    UNION ALL

    SELECT g.genre_id, g.name, g.parent_id, s.depth + 1
    FROM   genres g
    JOIN   subtree s ON g.parent_id = s.genre_id

)
SELECT genre_id, name, depth
FROM   subtree
WHERE  depth > 0                           -- exclude the root itself
ORDER BY depth, name;

Find the ancestors of a leaf node (walk upward)

Flip the direction: start at a leaf and JOIN to the parent on each iteration:

-- Breadcrumb path from "Space Opera" up to root
WITH RECURSIVE ancestors AS (

    SELECT genre_id, name, parent_id, 0 AS depth
    FROM   genres
    WHERE  name = 'Space Opera'

    UNION ALL

    SELECT g.genre_id, g.name, g.parent_id, a.depth - 1
    FROM   genres g
    JOIN   ancestors a ON g.genre_id = a.parent_id   -- walk UP via parent_id

)
SELECT name, depth
FROM   ancestors
ORDER BY depth;

name	depth
Fiction	-2
Science Fiction	-1
Space Opera	0

4. Depth Limiting and Cycle Detection

Recursive CTEs will loop forever if your data contains a cycle (A is the parent of B, B is the parent of A) or if the recursion has a bug. MySQL enforces a hard limit via the cte_max_recursion_depth system variable (default: 1000 iterations) — when hit, the query errors rather than running forever. For production code, add explicit guards.

Limit depth with a WHERE condition

WITH RECURSIVE genre_tree AS (

    SELECT genre_id, name, parent_id, 0 AS depth
    FROM   genres
    WHERE  parent_id IS NULL

    UNION ALL

    SELECT g.genre_id, g.name, g.parent_id, gt.depth + 1
    FROM   genres g
    JOIN   genre_tree gt ON g.parent_id = gt.genre_id
    WHERE  gt.depth < 5                    -- ← hard stop at depth 5

)
SELECT * FROM genre_tree ORDER BY depth, name;

Detect cycles with a path string

Track visited IDs in a path string. Before continuing, check that the next node's ID isn't already in the path:

WITH RECURSIVE safe_walk AS (

    SELECT
        genre_id,
        name,
        parent_id,
        0                                          AS depth,
        CAST(genre_id AS CHAR(1000))             AS visited_ids,
        FALSE                                      AS is_cycle
    FROM   genres
    WHERE  parent_id IS NULL

    UNION ALL

    SELECT
        g.genre_id,
        g.name,
        g.parent_id,
        sw.depth + 1,
        CONCAT(sw.visited_ids, ',', g.genre_id),
        FIND_IN_SET(g.genre_id, sw.visited_ids) > 0  AS is_cycle
    FROM   genres g
    JOIN   safe_walk sw ON g.parent_id = sw.genre_id
    WHERE  sw.is_cycle = FALSE                       -- stop if cycle found

)
SELECT * FROM safe_walk WHERE is_cycle = FALSE;

Change the recursion depth limit for deep trees: SET SESSION cte_max_recursion_depth = 5000; Only increase this for legitimate deep hierarchies — not to paper over an infinite loop bug. Always add a depth guard in your recursive member too.

5. Practical CTE Patterns

Generate a date series (no date table needed)

-- Generate every day in June 2026
WITH RECURSIVE date_series AS (
    SELECT DATE('2026-06-01') AS dt

    UNION ALL

    SELECT DATE_ADD(dt, INTERVAL 1 DAY)
    FROM   date_series
    WHERE  dt < '2026-06-30'
)
-- Left join to orders so days with zero orders still appear
SELECT
    ds.dt,
    COALESCE(COUNT(o.order_id), 0) AS orders_placed
FROM      date_series ds
LEFT JOIN orders o ON DATE(o.order_date) = ds.dt
GROUP BY  ds.dt
ORDER BY  ds.dt;

Date series are one of the most useful recursive CTE patterns. Generating a spine of dates and left-joining to transactional data ensures your report includes every day — even ones with no activity — without needing a pre-populated calendar table.

Generate a number series

-- Numbers 1 to 100 — useful for cross joins, test data, pagination
WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 100
)
SELECT n FROM nums;

CTE in an UPDATE or DELETE

CTEs can prefix UPDATE and DELETE statements too — useful for complex filtering:

-- Mark reviews as 'verified' where the customer actually purchased the book
WITH verified_purchases AS (
    SELECT DISTINCT
        oi.book_id,
        o.customer_id
    FROM order_items oi
    JOIN orders o ON o.order_id = oi.order_id
)
UPDATE reviews r
JOIN   verified_purchases vp
       ON  vp.book_id     = r.book_id
       AND vp.customer_id = r.customer_id
SET    r.verified = TRUE
WHERE  r.verified = FALSE;

Quick Reference

Concept	Syntax / rule	Notes
Basic CTE	`WITH name AS ( SELECT … ) SELECT … FROM name`	Scoped to the single statement. Vanishes after execution.
Multiple CTEs	`WITH a AS (…), b AS (…) SELECT …`	One `WITH`, comma-separated. Later CTEs can reference earlier ones.
Recursive CTE	`WITH RECURSIVE name AS ( anchor UNION ALL recursive ) SELECT …`	MySQL 8.0+ only. Must use `UNION ALL`. Runs until recursive member returns zero rows.
Depth limit	`WHERE depth < N` in the recursive member	Always add this for production queries on real (potentially dirty) data.
System depth cap	`SET SESSION cte_max_recursion_depth = N;`	Default 1000. Increase for legitimately deep trees, not to hide bugs.
Path / cycle tracking	Build a `visited_ids` string; check with `FIND_IN_SET()`	Guards against cycles in data that should be acyclic but isn't.
Date/number series	Recursive CTE starting at a seed value, adding 1 or 1 day each iteration	Left-join against transactions to include zero-activity periods in reports.
CTE + UPDATE/DELETE	`WITH … UPDATE table JOIN cte …`	Useful for complex filter logic before modifying rows. MySQL syntax — no `FROM` needed.
Materialisation hint	`WITH name AS ( SELECT /+ MATERIALIZE / … )`	Forces MySQL to evaluate the CTE into a temp table once. Useful if referenced multiple times.

Next: Chapter 6 — Window functions: OVER, PARTITION BY, ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and running totals with SUM OVER.