Recursive CTEs

A recursive CTE seeds itself with an anchor SELECT and repeats a recursive SELECT until it returns no rows.

SyntaxWITH RECURSIVE cte (cols) AS (anchor_select UNION [ALL|DISTINCT] recursive_select) SELECT ... FROM cte

A recursive CTE is one that refers to itself. It exists to express results that need an unknown number of steps: walking a parent chain, generating a series, splitting a string. Write WITH RECURSIVE — the keyword is required as soon as any CTE in the clause is self-referencing.

The two halves

The body is a union of two parts. The nonrecursive SELECT (the anchor) produces the starting rows and must not mention the CTE name. The recursive SELECT reads from the CTE and produces the next batch from the previous one. MySQL repeats the recursive half until an iteration returns zero rows, so the recursive half needs a condition that eventually fails.

UNION ALL keeps every row; UNION DISTINCT drops duplicates, which is one way to stop a cyclic graph from looping forever.

Restrictions

The recursive SELECT must not contain aggregate functions, window functions, GROUP BY, ORDER BY, or DISTINCT. It must reference the CTE exactly once, in its own FROM clause, never in a subquery and never on the right side of a LEFT JOIN. It may use LIMIT with an optional OFFSET, which stops row generation as soon as enough rows exist.

What trips people up

Runaway recursion is capped by cte_max_recursion_depth, default 1000 in MySQL 8.4; the server terminates the statement rather than returning partial rows. Raise it per statement with SET_VAR, or fix the termination condition.

Example

Example · sql
-- Daily revenue for March 2026 with zero-filled gaps.
-- The recursive CTE generates every date; the LEFT JOIN supplies the data.
WITH RECURSIVE calendar (day) AS (
    SELECT DATE '2026-03-01'                 -- anchor: one starting row
    UNION ALL
    SELECT day + INTERVAL 1 DAY              -- recursive half
    FROM calendar
    WHERE day < DATE '2026-03-31'            -- terminates when this is false
)
SELECT
    c.day,
    COALESCE(SUM(o.total), 0) AS revenue,
    COUNT(o.id)               AS orders
FROM calendar AS c
LEFT JOIN orders AS o
       ON o.placed_at >= c.day
      AND o.placed_at <  c.day + INTERVAL 1 DAY
GROUP BY c.day
ORDER BY c.day;

When to use it

  • Generate a gap-free date or number series to join reports against.
  • Compute a running sequence where each row depends on the previous one.
  • Split a delimited column into one row per value so it can be joined.
  • Expand a bill of materials or dependency list to an unknown depth.

More examples

Fibonacci: carrying state between iterations

The recursive half can only see the previous iteration's rows, so any value the next step needs must be carried as a column. Here `next_fib_n` is the lookahead that makes the sequence computable one row at a time — the same trick you use to carry a running balance or a path string.

Example · sql
WITH RECURSIVE fibonacci (n, fib_n, next_fib_n) AS (
    SELECT 1, 0, 1                                   -- anchor
    UNION ALL
    SELECT n + 1, next_fib_n, fib_n + next_fib_n     -- each row derives from the last
    FROM fibonacci
    WHERE n < 10
)
SELECT n, fib_n FROM fibonacci;

LIMIT inside the recursive SELECT

There is no terminating WHERE here — LIMIT is the brake. Putting it in the recursive part stops generation as soon as 10,000 rows exist, unlike a LIMIT on the outer SELECT which generates everything first. The SET_VAR hint raises the 1000-level default for this statement only.

Example · sql
-- 10,000 rows of a counter, stopped at the source rather than at the top.
WITH RECURSIVE seq (n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM seq
    LIMIT 10000                       -- allowed in the recursive part; ORDER BY is not
)
SELECT /*+ SET_VAR(cte_max_recursion_depth = 20000) */ n
FROM seq;

Splitting a delimited string into rows

A practical use for recursion that has nothing to do with trees: turning a comma-separated column into rows so it can be joined. Note the CAST in the anchor — without it the columns are sized from the literal and every later token is truncated.

Example · sql
WITH RECURSIVE split (rest, token) AS (
    -- CAST sizes the columns; the recursive half cannot widen them.
    SELECT CONCAT(CAST('alpha,beta,gamma' AS CHAR(1000)), ','),
           CAST('' AS CHAR(1000))
    UNION ALL
    SELECT SUBSTRING(rest, LOCATE(',', rest) + 1),
           SUBSTRING_INDEX(rest, ',', 1)
    FROM split
    WHERE rest <> ''
)
SELECT token FROM split WHERE token <> '';

Related

sql hierarchiessql withsql unionsql cte materializationsql date functions

Discussion

  • Be the first to comment on this lesson.