Recursive CTEs

Concept

A recursive CTE refers to itself. It has two halves joined by UNION ALL:

• The anchor member - runs once and produces the starting rows.
• The recursive member - joins the CTE to a table, and runs repeatedly. Each pass sees only the rows the previous pass produced.

Recursion stops when a pass returns no rows.

Syntax

WITH RECURSIVE cte_name AS (
    SELECT ...                      -- anchor member
    UNION ALL
    SELECT ... FROM table_name JOIN cte_name ON ...   -- recursive member
)
SELECT * FROM cte_name;

MySQL, MariaDB, PostgreSQL and SQLite require the RECURSIVE keyword. SQL Server and Oracle do not use it - a plain WITH is recursive if it references itself.

Example: an organisation chart

WITH RECURSIVE chain AS (
    -- anchor: the person with no manager
    SELECT id, first_name, manager_id, 1 AS level,
           CAST(first_name AS CHAR(200)) AS path
    FROM   employees
    WHERE  manager_id IS NULL

    UNION ALL

    -- recursive: everyone reporting to a row already in `chain`
    SELECT e.id, e.first_name, e.manager_id, c.level + 1,
           CONCAT(c.path, ' > ', e.first_name)
    FROM   employees e
    JOIN   chain c ON c.id = e.manager_id
)
SELECT level, first_name, path
FROM   chain
ORDER BY level, first_name;

Explanation

The anchor finds Asha, who reports to nobody. Pass one joins employees to that single row and finds everyone whose manager_id is Asha's id. Pass two expands those, and so on. When a pass finds no new reports, the recursion ends.

level and path are built up as the recursion proceeds - each pass adds one to the level and appends a name. The explicit CAST(... AS CHAR(200)) in the anchor matters: the column type is fixed by the anchor, and without it MySQL sizes path to the first name it sees and then truncates every deeper path.

Example: generating a series

-- Every day of January 2024, with no calendar table
WITH RECURSIVE days AS (
    SELECT DATE('2024-01-01') AS d
    UNION ALL
    SELECT DATE_ADD(d, INTERVAL 1 DAY) FROM days WHERE d < '2024-01-31'
)
SELECT d FROM days;

This is the standard way to build a complete date range so a report can show zero rows for days with no activity - left join the real data onto the generated days.

Important rules

• The anchor member must come first and must not reference the CTE.
• The recursive member must reference the CTE exactly once.
• UNION ALL is required by most engines; UNION is allowed by some and adds a de duplication pass per iteration.
• Column names and types are fixed by the anchor member. Cast in the anchor when a column grows.
• Every engine has a recursion limit: MySQL's cte_max_recursion_depth defaults to 1000, SQL Server's MAXRECURSION to 100. Both are adjustable.
• Aggregates, ORDER BY and LIMIT are not allowed inside the recursive member in most products.

Common mistakes

• Omitting the termination condition and running until the depth limit errors out.
• Cyclic data - A manages B, B manages A - producing infinite recursion. Guard it by carrying a path and checking membership, or by capping level.
• Forgetting the RECURSIVE keyword on MySQL, MariaDB or PostgreSQL.
• Not casting a growing string column in the anchor, and getting truncated paths.
• Using a recursive CTE for a fixed two level hierarchy where a simple self join is clearer.

Best practices

• Always carry a level column and add WHERE level < 50 as a safety net on untrusted data.
• Build a path column - it makes the output readable and lets you detect cycles.
• Cast string columns to their final width in the anchor.
• Index the column the recursion joins on - usually manager_id or parent_id.
• For deep hierarchies read very often, consider storing a materialised path or a closure table instead.

Practice

1. Return every employee who reports, directly or indirectly, to Asha.
2. Generate every month of 2024 as a single column result.
3. Add a cycle to the sample data deliberately, then add the guard that stops the query running forever.