Recursive CTEs in SQL

A recursive CTE references itself, letting you walk hierarchies, generate sequences, and traverse graphs in pure SQL. The structure is always the same: a base case (the "anchor" query), a UNION ALL, and a recursive case that references the CTE name. Without the base case there's nothing to start from; without a terminating join condition you get an infinite loop.

The Anatomy of a Recursive CTE

WITH RECURSIVE cte_name AS (
    -- 1. Anchor: rows that start the recursion
    SELECT ...
    FROM ...
    WHERE ...

    UNION ALL

    -- 2. Recursive step: references cte_name
    SELECT ...
    FROM ...
    JOIN cte_name ON ...
)
SELECT * FROM cte_name;

The recursive step runs over and over — at each iteration it sees only the rows the previous iteration produced — until it returns no new rows.

Example 1: Walking an Org Chart (Hierarchy)

Given a self-referencing employees table with a manager_id, list everyone who reports up to a given CEO.

WITH RECURSIVE reports AS (
    -- Anchor: the CEO
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE id = 1

    UNION ALL

    -- Recursive step: anyone whose manager is already in `reports`
    SELECT e.id, e.name, e.manager_id, r.depth + 1
    FROM employees e
    JOIN reports r ON e.manager_id = r.id
)
SELECT id, name, depth FROM reports ORDER BY depth, name;

The depth column makes it easy to indent or filter to direct reports vs the whole subtree.

Example 2: Generating a Date Spine

Need every day in a range — even days with zero events? Generate a date series, then left-join your facts.

WITH RECURSIVE date_spine AS (
    SELECT DATE '2026-01-01' AS d
    UNION ALL
    SELECT d + INTERVAL '1 day'
    FROM date_spine
    WHERE d < DATE '2026-01-31'
)
SELECT
    s.d,
    COALESCE(COUNT(o.id), 0) AS orders
FROM date_spine s
LEFT JOIN orders o ON o.created_date = s.d
GROUP BY s.d
ORDER BY s.d;

Postgres has generate_series() for this; recursive CTEs are the portable fallback for SQL Server / MySQL / SQLite.

Example 3: Graph Traversal With Cycle Detection

For a graph (e.g., friend-of-friend), you must guard against cycles or the query will loop until it runs out of memory.

WITH RECURSIVE reachable AS (
    SELECT
        from_user AS user_id,
        ARRAY[from_user] AS path,        -- Postgres array
        1 AS depth
    FROM friendships
    WHERE from_user = 100

    UNION ALL

    SELECT
        f.to_user,
        r.path || f.to_user,
        r.depth + 1
    FROM friendships f
    JOIN reachable r ON f.from_user = r.user_id
    WHERE NOT (f.to_user = ANY(r.path))   -- skip already-visited nodes
      AND r.depth < 5                    -- bound the search
)
SELECT DISTINCT user_id FROM reachable;

The path array is the cycle guard. The depth < 5 clause stops a runaway recursion even if the cycle check has a bug.

Termination: How the Recursion Stops

Each iteration, the engine runs the recursive step against the rows produced by the previous iteration. When the step returns zero rows, the recursion stops. Three ways things go wrong:

• Cycles in the data — A reports to B, B reports to A. Add a path tracker or a WHERE depth < N bound.
• Cartesian recursive step — joining without a proper key can multiply rows each iteration.
• Wrong direction — joining on the parent side instead of child side never narrows the result. Trace one iteration on paper.

Dialect Notes

• PostgreSQL: requires WITH RECURSIVE. Supports SEARCH and CYCLE clauses (Postgres 14+) for built-in cycle detection.
• MySQL 8+: requires WITH RECURSIVE. Default recursion limit is 1000 — adjust with SET cte_max_recursion_depth = ....
• SQL Server: the keyword is just WITH (no RECURSIVE). Add OPTION (MAXRECURSION 1000) at the end of the outer query, or 0 for unlimited.
• SQLite: requires WITH RECURSIVE. No depth limit by default — write your own.

When Not to Use a Recursive CTE

• Fixed-depth hierarchies (e.g., always 3 levels) — a chain of self-joins is faster.
• Heavy graph workloads — recursive CTEs in a relational database don't index the recursion. A graph database or a materialized closure table will outperform them at scale.
• Date sequences in Postgres — use generate_series(); in BigQuery use UNNEST(GENERATE_DATE_ARRAY(...)).

Common Pitfalls

• Forgetting UNION ALL vs UNION: UNION deduplicates each iteration, which is slow and changes semantics. Use UNION ALL unless you specifically need dedup.
• No anchor: the base case must reference real tables, not the CTE itself, or the whole recursion is empty.
• Different column counts/types in the two halves: the anchor and recursive halves must produce identical column sets — same names, same types.
• Hidden infinite loops in dev: always set a MAXRECURSION / depth bound while developing, even if you remove it later.