Recursive CTEs

Regular CTEs are powerful for organizing queries, but recursive CTEs take it further: they allow a query to reference itself, making it possible to traverse hierarchical and graph-like data structures.

What Is a Recursive CTE?

A recursive CTE is a CTE that references its own name within its definition. This creates a loop where each iteration builds upon the results of the previous one, until no new rows are produced.

Syntax

WITH RECURSIVE cte_name AS (
  -- Anchor member (base case): does NOT reference cte_name
  SELECT ...

  UNION ALL

  -- Recursive member: references cte_name
  SELECT ...
  FROM cte_name
  JOIN other_table ON ...
)
SELECT * FROM cte_name;

How It Works

Iteration 0: Run the ANCHOR member
             Save results to cte_name
             
Iteration 1: Run the RECURSIVE member using Iteration 0's results
             Append new results to cte_name
             
Iteration 2: Run the RECURSIVE member using Iteration 1's NEW results
             Append new results to cte_name
             
... continues until the recursive member returns 0 new rows ...

Final: Return ALL accumulated results

A Simple Example: Counting

Before we dive into hierarchical data, let's see a simple recursive CTE that generates numbers:

WITH RECURSIVE counter AS (
  -- Anchor: start at 1
  SELECT 1 AS n

  UNION ALL

  -- Recursive: add 1 each time
  SELECT n + 1
  FROM counter
  WHERE n < 10  -- stop condition
)
SELECT n FROM counter;

This produces the numbers 1 through 10:

Iteration 0 (anchor): n = 1
Iteration 1: n = 1 + 1 = 2
Iteration 2: n = 2 + 1 = 3
...
Iteration 9: n = 9 + 1 = 10
Iteration 10: n = 10 + 1 = 11, but WHERE n < 10 stops it (10 is not < 10)
Wait -- actually n=10 was produced in iteration 9. The recursive member
runs with n=10 and tries n+1=11, but the WHERE condition filters it,
returning 0 rows. Recursion stops.

Traversing the Employee Hierarchy

Our employees table has a manager_id column creating a hierarchy. Let's traverse it with a recursive CTE.

Building the Full Org Chart

WITH RECURSIVE org_chart AS (
  -- Anchor: start with top-level managers (no manager)
  SELECT
    id,
    name,
    department,
    manager_id,
    1 AS level,
    name AS management_chain
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: find direct reports of current level
  SELECT
    e.id,
    e.name,
    e.department,
    e.manager_id,
    oc.level + 1,
    oc.management_chain || ' -> ' || e.name
  FROM employees e
  INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT
  level,
  name,
  department,
  management_chain
FROM org_chart
ORDER BY management_chain;

Let's walk through this step by step:

Iteration 0 (Anchor):
  Find employees where manager_id IS NULL
  Result: Alice (level 1, chain: "Alice")

Iteration 1 (Recursive):
  Find employees whose manager_id matches Alice's id
  Result: Bob (level 2, chain: "Alice -> Bob")
          Charlie (level 2, chain: "Alice -> Charlie")
          Diana (level 2, chain: "Alice -> Diana")

Iteration 2 (Recursive):
  Find employees whose manager_id matches Bob, Charlie, or Diana's id
  Result: Frank (level 3, chain: "Alice -> Bob -> Frank")
          Eve (level 3, chain: "Alice -> Charlie -> Eve")
          Grace (level 3, chain: "Alice -> Charlie -> Grace")
          Hank (level 3, chain: "Alice -> Diana -> Hank")

Iteration 3 (Recursive):
  Find employees whose manager_id matches Frank, Eve, Grace, or Hank's id
  Result: (no rows) --> recursion stops

Visualizing the Result

Level  Name      Chain
-----  --------  ----------------------------------
1      Alice     Alice
2      Bob       Alice -> Bob
3      Frank     Alice -> Bob -> Frank
2      Charlie   Alice -> Charlie
3      Eve       Alice -> Charlie -> Eve
3      Grace     Alice -> Charlie -> Grace
2      Diana     Alice -> Diana
3      Hank      Alice -> Diana -> Hank

Indented Org Chart Display

You can use the level to create a visual indentation:

WITH RECURSIVE org_chart AS (
  SELECT
    id,
    name,
    manager_id,
    0 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT
    e.id,
    e.name,
    e.manager_id,
    oc.level + 1
  FROM employees e
  INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT
  REPEAT('  ', level) || name AS org_structure,
  level
FROM org_chart
ORDER BY level, name;

Output:

org_structure         level
--------------------  -----
Alice                 0
  Bob                 1
  Charlie             1
  Diana               1
    Eve               2
    Frank             2
    Grace             2
    Hank              2

Finding All Reports (Direct and Indirect)

Find everyone who reports to a specific manager, at any level:

WITH RECURSIVE all_reports AS (
  -- Anchor: start with the target manager's direct reports
  SELECT id, name, manager_id, 1 AS depth
  FROM employees
  WHERE manager_id = (SELECT id FROM employees WHERE name = 'Alice')

  UNION ALL

  -- Recursive: find reports of reports
  SELECT e.id, e.name, e.manager_id, ar.depth + 1
  FROM employees e
  INNER JOIN all_reports ar ON e.manager_id = ar.id
)
SELECT name, depth
FROM all_reports
ORDER BY depth, name;

This returns everyone who directly or indirectly reports to Alice.

Calculating Hierarchy Depth

Find the depth of the management chain for each employee:

WITH RECURSIVE hierarchy AS (
  SELECT id, name, manager_id, 0 AS depth
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, h.depth + 1
  FROM employees e
  INNER JOIN hierarchy h ON e.manager_id = h.id
)
SELECT
  name,
  depth,
  CASE depth
    WHEN 0 THEN 'CEO / Top Level'
    WHEN 1 THEN 'Senior Manager'
    WHEN 2 THEN 'Team Member'
    ELSE 'Level ' || depth
  END AS role_level
FROM hierarchy
ORDER BY depth, name;

Preventing Infinite Loops

If your data has cycles (employee A manages B, B manages A), the recursive CTE will loop forever. Protect against this:

Using a Maximum Depth

WITH RECURSIVE org_chart AS (
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  INNER JOIN org_chart oc ON e.manager_id = oc.id
  WHERE oc.level < 10  -- safety limit: stop after 10 levels
)
SELECT * FROM org_chart;

Using UNION Instead of UNION ALL

UNION (without ALL) automatically removes duplicate rows, which can break cycles. However, this is less efficient and only works when the exact same row would be produced again.

Recursive CTEs: Key Rules

1. Must use WITH RECURSIVE -- the RECURSIVE keyword is required
2. Anchor member comes first -- it provides the starting rows
3. Connected by UNION ALL (or UNION) -- combines anchor and recursive results
4. Recursive member references the CTE -- that is what makes it recursive
5. Terminates when no new rows are produced -- always ensure termination
6. Each iteration only sees the PREVIOUS iteration's new rows -- not all accumulated rows

Try It Yourself

1. Write a recursive CTE to generate the numbers 1 through 20.

2. Using the employees table, write a recursive CTE that shows the full management chain for each employee (from the top-level manager down to them). Include the level depth.

3. Find all employees who report to Charlie (directly or indirectly) using a recursive CTE. Show each person's name and how many levels below Charlie they are.

4. Create an indented org chart that shows each employee's name with indentation proportional to their level in the hierarchy.