Recursive CTEs

Module 4's self-join found each employee's direct manager, one level up. It can't find everyone under a given manager at every level, 2nd-line reports, 3rd-line reports, however deep the chain goes, a plain JOIN only ever reaches a fixed number of levels. A recursive CTE reaches all of them, however deep that actually is.

Learning Objectives

• Define a recursive CTE, and explain the anchor member / recursive member structure.
• Write a recursive CTE walking Beacon Robotics' manager hierarchy, correctly on multiple databases.
• Know which databases require the literal word RECURSIVE, and which don't use it at all.

The Anchor and Recursive Members

A recursive CTE has two parts, joined by UNION ALL: the anchor member, a normal query that produces the starting rows, and the recursive member, a query that joins back to the CTE's own name, pulling in one more level each time it runs. The database keeps re-running the recursive member, feeding each round's results into the next, until a round produces zero new rows.

Walking the Full Manager Hierarchy

The anchor finds every employee with no manager (depth 1, the top of the chain). The recursive member joins employees back to org_chart itself, finding each next level's direct reports and incrementing depth, round after round, until a round finds nobody left to add.

WITH RECURSIVE org_chart AS (
  SELECT id, first_name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.first_name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart
ORDER BY depth;

WITH org_chart AS (
  SELECT id, first_name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.first_name, e.manager_id, oc.depth + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart
ORDER BY depth;

The Real RECURSIVE Keyword Split

Guarding Against an Infinite Loop

A recursive CTE with no natural termination, or a cycle in the underlying data (A manages B, B manages A), can run indefinitely. SQL Server is the one database here with a built-in safety net: a default MAXRECURSION limit of 100 levels, erroring out past that unless explicitly raised with OPTION (MAXRECURSION 1000) or removed entirely with OPTION (MAXRECURSION 0). The other five databases have no equivalent built-in cap, a genuinely cyclic query there can run until manually stopped.

Try It Yourself

Write a recursive CTE listing every employee who reports, directly or indirectly, to the employee with id 1, on PostgreSQL.

WITH RECURSIVE reports AS (
  SELECT id, first_name, manager_id
  FROM employees
  WHERE manager_id = 1

  UNION ALL

  SELECT e.id, e.first_name, e.manager_id
  FROM employees e
  JOIN reports r ON e.manager_id = r.id
)
SELECT * FROM reports;

Frequently Asked Questions

Common Beginner Mistakes

Best Practices

• Always include a depth or path column, incrementing it in the recursive member, both to track levels and to cap runaway recursion if needed.
• Test a recursive CTE against a small, known subset of data first, before running it against the full table.
• Remember the RECURSIVE keyword's split: required on MySQL/MariaDB/PostgreSQL/SQLite, absent (and invalid) on SQL Server/Oracle.

Interview Questions

Summary

A recursive CTE references itself, an anchor member provides the starting rows, a recursive member joins back to the CTE's own name, repeating until a round produces nothing new, the only way to walk a hierarchy to an unknown depth. MySQL, MariaDB, PostgreSQL, and SQLite require the literal RECURSIVE keyword, SQL Server and Oracle don't have it at all and detect the self-reference automatically. SQL Server's default MAXRECURSION of 100 is the one built-in runaway-recursion safeguard among these six.

That completes the Common Table Expressions module: naming a result with WITH instead of nesting a subquery, chaining multiple named steps, and recursion for the one problem a plain JOIN genuinely can't solve.

What's Next?

Module 13, Indexes and Performance, is next: how an index actually makes a lookup fast, and reading an execution plan to see it for real.