Common table expressions and recursion

Key points

• A CTE is a named subquery declared with WITH before the main statement. Its value is readability - a query built from three named steps beats three levels of nesting.
• Several CTEs can be chained, and a later one may reference an earlier one, which lets a complex transformation read top to bottom.
• MySQL 8 does not always materialise a CTE. It may merge it into the outer query like a view, so a CTE referenced twice can be executed twice - check EXPLAIN rather than assuming.
• A recursive CTE has two parts joined by UNION ALL: an anchor that produces the starting rows, and a recursive member that references the CTE itself.
• That is how you walk a hierarchy - an org chart, a category tree, a bill of materials - in one statement instead of a loop in application code.
• Always bound the recursion. cte_max_recursion_depth defaults to 1000, and a cycle in the data will otherwise run until it hits that limit and fails.

Example

-- Chained CTEs: each step named, read top to bottom.
WITH recent_orders AS (
    SELECT * FROM orders WHERE created_at >= CURRENT_DATE - INTERVAL 30 DAY
),
customer_totals AS (
    SELECT customer_id, SUM(total) AS spend, COUNT(*) AS order_count
    FROM recent_orders          -- references the CTE above
    GROUP BY customer_id
)
SELECT c.name, t.spend, t.order_count
FROM customer_totals t
JOIN customers c ON c.id = t.customer_id
WHERE t.spend > 1000
ORDER BY t.spend DESC;

-- RECURSIVE: walk an org chart from one manager downwards.
WITH RECURSIVE reports AS (
    -- anchor: where the walk starts
    SELECT id, name, manager_id, 1 AS depth,
           CAST(name AS CHAR(1000)) AS path
    FROM employees
    WHERE id = 42

    UNION ALL

    -- recursive member: joins the CTE back to the table
    SELECT e.id, e.name, e.manager_id, r.depth + 1,
           CONCAT(r.path, ' > ', e.name)
    FROM employees e
    JOIN reports r ON e.manager_id = r.id
    WHERE r.depth < 10          -- ALWAYS bound it: cycles in data are real
)
SELECT depth, path FROM reports ORDER BY depth, path;

--   depth  path
--   1      Alice
--   2      Alice > Bob
--   3      Alice > Bob > Carol

-- A recursive CTE also generates series - useful for filling date gaps:
WITH RECURSIVE days AS (
    SELECT CURRENT_DATE - INTERVAL 29 DAY AS d
    UNION ALL
    SELECT d + INTERVAL 1 DAY FROM days WHERE d < CURRENT_DATE
)
SELECT days.d, COALESCE(SUM(s.amount), 0) AS total
FROM days LEFT JOIN sales s ON DATE(s.created_at) = days.d
GROUP BY days.d;

CTEs name the steps of a query, and a recursive CTE walks a hierarchy in one statement - always with a depth bound.