Useful Data Tips
Home / SQL Tips / Recursive CTEs

Recursive CTE in SQL: Syntax, Step-by-Step Trace, and Cycle Guards

⏱️ 3 min read 🗄️ 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.

Quick answer: A recursive CTE is a WITH RECURSIVE query made of two parts joined by UNION ALL: an anchor query that produces the starting rows, and a recursive query that references the CTE itself. The engine re-runs the recursive part against the previous iteration's rows until it returns no new rows, making it the standard SQL tool for org charts, trees, and sequences.

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.

How Does a Recursive CTE Actually Execute?

Iteratively, not by function-call recursion. The anchor runs once and its rows become the working set. Each iteration, the recursive step joins only against the rows produced by the previous iteration, appends its output to the result, and that output becomes the new working set. When an iteration produces zero rows, execution stops.

Trace it on a five-person org chart — Priya (id 1, CEO), Marcus (2) and Elena (3) report to Priya, Jonah (4) reports to Marcus, Ava (5) reports to Jonah:

-- Iteration 0 (anchor):  WHERE id = 1
--   -> (1, 'Priya',  depth 0)             working set: {1}
-- Iteration 1: employees whose manager_id IN (1)
--   -> (2, 'Marcus', depth 1)
--   -> (3, 'Elena',  depth 1)             working set: {2, 3}
-- Iteration 2: employees whose manager_id IN (2, 3)
--   -> (4, 'Jonah',  depth 2)             working set: {4}
-- Iteration 3: employees whose manager_id IN (4)
--   -> (5, 'Ava',    depth 3)             working set: {5}
-- Iteration 4: employees whose manager_id IN (5)
--   -> no rows                            recursion stops
-- Final result: all 5 rows, depths 0-3

Note that iteration 2 does not re-scan Priya's row — only the previous iteration's rows ({2, 3}) feed the join. That's what keeps each step cheap and the whole walk linear in the number of reachable rows.

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.

When Does a Recursive CTE Stop (and How Do You Prevent Infinite Loops)?

A recursive CTE stops the first time the recursive step returns zero rows — there is no other built-in exit. You prevent infinite loops three ways: make the join condition genuinely narrow the data (child-to-parent, not parent-to-child), track visited rows with a path column for cyclic data, and add an explicit depth bound as a safety net. Three ways things go wrong:

Which Databases Support Recursive CTEs — and How Does the Syntax Differ?

All major engines support them: PostgreSQL, MySQL 8+, SQL Server, Oracle, SQLite, BigQuery, and Snowflake. The main syntax split is the keyword — PostgreSQL, MySQL, and SQLite require WITH RECURSIVE, while SQL Server and Oracle use plain WITH. Recursion depth limits and cycle-detection helpers also vary by engine:

When Not to Use a Recursive CTE

Common Pitfalls

New to CTEs in general? Start with CTEs vs subqueries for when the non-recursive form is the right tool.

Pro Tip: If you find yourself writing the same recursive CTE on the same table over and over, materialize it as a closure table — a flat table of (ancestor, descendant, depth) pairs maintained on insert. Reads become a simple indexed lookup with no recursion at all.

← Back to SQL Tips

Related Tips

Written by Aaron Pitt · Useful Data Tips