Recursive CTEs

Some questions have no fixed depth: "who reports to whom, all the way down?" or "give me match numbers 1 to N" with no table of numbers. A recursive CTE solves these by repeating a step until there is nothing new to add.

Definition

A recursive CTE is a CTE that refers to itself. It is made of an anchor member (a query that runs once and produces the starting rows), UNION ALL, and a recursive member (a query that reads the CTE's own previous output and produces the next rows). The recursion stops automatically when the recursive member returns no new rows.

Syntax

WITH cte_name AS (
    -- 1. anchor member (runs once)
    SELECT start_columns
    FROM ...
    UNION ALL
    -- 2. recursive member (refers to cte_name)
    SELECT next_columns
    FROM cte_name
    WHERE stop_condition
)
SELECT * FROM cte_name;

Note: SQL Server uses plain WITH — no RECURSIVE keyword. PostgreSQL and MySQL require WITH RECURSIVE.

Example — generate match numbers 1 to 5

WITH matches AS (
    SELECT 1 AS n                -- anchor
    UNION ALL
    SELECT n + 1                 -- recursive
    FROM matches
    WHERE n < 5                  -- stop condition
)
SELECT n AS match_no
FROM matches;

How it works:

No table is needed — SQL builds the rows by itself.

Example — walk a hierarchy (team_staff)

The team_staff table stores who reports to whom: boss_id points to the id of the person's boss.

WITH org AS (
    -- anchor: the top boss (no boss of their own)
    SELECT id, name, boss_id, 1 AS level
    FROM team_staff
    WHERE boss_id IS NULL
    UNION ALL
    -- recursive: everyone whose boss is already in org
    SELECT s.id, s.name, s.boss_id, o.level + 1
    FROM team_staff AS s
    JOIN org AS o
        ON s.boss_id = o.id
)
SELECT id, name, level
FROM org
ORDER BY level, id;

How it works: the anchor finds the owner (level 1). Round 1 finds everyone whose boss is the owner → Head Coach (level 2). Round 2 finds the Captain and Batting Coach (level 3). Round 3 finds the Vice Captain (level 4). Round 4 finds nobody → stop. Here the join is the stop condition.

Example — show the full reporting path

Add a text column that grows on each level:

WITH org AS (
    SELECT id, name, 1 AS level,
           CAST(name AS VARCHAR(200)) AS path
    FROM team_staff
    WHERE boss_id IS NULL
    UNION ALL
    SELECT s.id, s.name, o.level + 1,
           CAST(CONCAT(o.path, ' > ', s.name) AS VARCHAR(200))
    FROM team_staff AS s
    JOIN org AS o
        ON s.boss_id = o.id
)
SELECT id, name, level, path
FROM org
ORDER BY level, id;

Tip: In SQL Server the anchor and recursive columns must have exactly the same data types. That is why path is CAST to VARCHAR(200) in both parts — without it SQL Server raises "Types don't match between the anchor and the recursive part".

Infinite loops and MAXRECURSION

If the stop condition is missing (SELECT n + 1 FROM matches with no WHERE), the recursion never ends. SQL Server protects you: by default it stops after 100 recursion levels with an error. You can change the limit:

WITH numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 500
)
SELECT n FROM numbers
OPTION (MAXRECURSION 1000);   -- 0 = no limit (dangerous)

Common mistakes

• ❌ Missing stop condition → infinite loop (SQL Server error after 100 levels). ✅ always add WHERE n < … or a join that eventually finds nothing.
• ❌ UNION instead of UNION ALL → SQL Server requires UNION ALL between anchor and recursive member.
• ❌ Different data types in anchor and recursive parts. ✅ CAST both sides to the same type.
• ❌ Writing WITH RECURSIVE in SQL Server → syntax error. ✅ plain WITH.
• ❌ Expecting hierarchy output in a particular order. ✅ add ORDER BY level (or by path).

Interview tip

Q: Find all employees under a given manager, at any depth. Use a recursive CTE: the anchor selects the manager (WHERE id = @manager_id), the recursive member joins the table to the CTE on boss_id = cte.id. This is the classic interview use case for recursive CTEs.