SQL Recursive CTEs With Examples

Why a normal query can't do this

If you've read CTEs vs Subqueries, you already know recursion is the one thing a subquery can't touch at all. Here's why a regular query can't either: to find "everyone under Priya, at any depth," you'd need to self-join employees to itself once per level — one join for direct reports, two for reports-of-reports, three for the level after that. That works only if you know the hierarchy is exactly N levels deep. Add one more layer next year, and the query silently misses people. A recursive CTE doesn't need to know the depth ahead of time — it keeps going until there's nothing left to find.

Anatomy of a recursive CTE

Every recursive CTE has exactly two parts, joined by UNION ALL:

WITH RECURSIVE cte_name AS (

  -- 1) ANCHOR MEMBER: runs once, no self-reference
  SELECT ...
  FROM some_table
  WHERE ...

  UNION ALL

  -- 2) RECURSIVE MEMBER: references cte_name itself
  SELECT ...
  FROM some_table
  JOIN cte_name ON ...

)
SELECT * FROM cte_name;

The anchor is a completely normal query — it defines the starting point and runs exactly once. The recursive member references cte_name in its own FROM/JOIN, and keeps re-running — each time seeing only the rows the previous round produced — until a round produces zero new rows, at which point it stops.

A full worked example: the org chart

Here's a small company. Notice Ravi reports to Tom, who reports to Priya, who reports to Nina — four levels deep, with no fixed number of joins that would cleanly capture "everyone below Nina":

The recursive CTE, with a level column to track depth as we go:

WITH RECURSIVE org_chart AS (

  -- Anchor: start at the top (no manager)
  SELECT employee_id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: find direct reports of everyone found so far
  SELECT e.employee_id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.employee_id

)
SELECT * FROM org_chart ORDER BY level, name;

All eight employees, correctly leveled, without ever writing "how many joins deep" into the query.

How it actually runs, round by round

Think of it as a loop that keeps feeding its own output back in as input:

• Round 1 (anchor): finds Nina — the only row with no manager. Level 1.
• Round 2: the recursive member joins employees against Round 1's rows — finds everyone whose manager_id is Nina's employee_id: Omar and Priya. Level 2.
• Round 3: joins employees against Round 2's rows — finds everyone reporting to Omar or Priya: Jake, Wei, Tom, Ella. Level 3.
• Round 4: joins against Round 3's rows — finds Ravi, who reports to Tom. Level 4.
• Round 5: joins against Round 4's rows — nobody reports to Ravi. Zero new rows. Recursion stops.

Building a readable path

A small addition turns the flat level number into a full breadcrumb trail — handy for displaying "where does this person sit" at a glance:

WITH RECURSIVE org_chart AS (
  SELECT employee_id, manager_id,
         CAST(name AS CHAR(200)) AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.employee_id, e.manager_id,
         CONCAT(oc.path, ' > ', e.name)
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.employee_id
)
SELECT path FROM org_chart WHERE employee_id = 8;
-- Nina (CEO) > Priya (VP Engineering) > Tom (Engineer) > Ravi (Junior Engineer)

Notice the CAST(name AS CHAR(200)) in the anchor — the path grows longer with every level, so the anchor's column type has to be wide enough up front, or the recursive member's longer strings will get silently truncated to match it.

Preventing infinite loops

If the data ever contains a cycle — say, a data-entry error makes someone their own indirect manager — a recursive CTE with no safeguard could loop forever. Two protections, usually used together:

• A depth cap in the recursive member: add WHERE oc.level < 20 (or whatever a sane maximum is) to stop after a set number of rounds no matter what.
• The database's built-in limit: most databases refuse to recurse forever by default — SQL Server's default MAXRECURSION is 100, and it errors out loudly instead of hanging, which is a useful early warning that your data has a cycle.

Syntax differences by database

Common mistakes

• Using UNION instead of UNION ALL. UNION forces a deduplication check against the whole growing result every round, which is slower and rarely needed for tree-shaped data.
• Referencing the CTE more than once in the recursive member. Only one reference is allowed, and it must be a direct join — not inside a subquery.
• No cycle protection. Real-world hierarchy data occasionally has bad rows; a depth cap costs nothing and saves you from a runaway query.
• Mismatched column types between anchor and recursive member. A growing text column (like a path) needs to be cast wide enough in the anchor, or later rounds get truncated.