SQL Recursive CTEs

In Employee Directory, every employee's manager_id points at another row of the same table. A self-join follows that pointer one step: an employee and their manager. Two steps takes two joins, and the org chart here is five levels deep, so a query that walks it with joins has to know the depth in advance.

A recursive CTE doesn't. It is a CTE that selects from itself, and it keeps going until a pass finds no new rows.

Anchor, then recursive part

This one starts at Hugo Marchetti, a junior engineer, and climbs to the top:

with recursive chain as (
  -- anchor: the starting row
  select employee_id, first_name, last_name, manager_id, 0 as steps
  from employees
  where employee_id = 9

  union all

  -- recursive part: one more row per pass
  select m.employee_id, m.first_name, m.last_name, m.manager_id, chain.steps + 1
  from employees as m
  join chain on m.employee_id = chain.manager_id
)
select steps, first_name, last_name
from chain
order by steps

It has two parts, joined by union all:

• The anchor runs once and gives the starting rows: Hugo, at step 0.
• The recursive part joins the table to the rows the previous pass produced. Pass one finds Hugo's manager, Tomas Ferreira; pass two finds Elena Sokolov, then Marcus Bell, then Dana Whitfield, the CEO.

Dana has no manager, so the next pass joins to nothing, and the recursion stops. The result is the whole chain, five rows, steps 0 to 4. The steps column is a counter you carry yourself: each pass adds one to the value in the row it came from.

Which way the join points

The direction you walk is decided by the join in the recursive part, and it is the line people get backwards most often.

• Up the chain (an employee's managers): m.employee_id = chain.manager_id. Find the row whose id is the current row's manager.
• Down the tree (everyone under a manager): e.manager_id = org.employee_id. Find the rows whose manager is the current row.

Going up, each pass finds at most one row. Going down, a pass can find many: one manager with four reports produces four rows in the next pass.

Carrying a path along

Anything the recursive part computes from the previous row accumulates. Glue names together and every row records how it was reached:

with recursive org as (
  select employee_id,
         first_name || ' ' || last_name as path,
         0 as depth
  from employees
  where manager_id is null

  union all

  select e.employee_id,
         org.path || ' > ' || e.first_name || ' ' || e.last_name,
         org.depth + 1
  from employees as e
  join org on e.manager_id = org.employee_id
)
select depth, path
from org
order by path

Starting from the one employee with no manager, this returns all 18 people, and sorting by the path puts each person directly under their manager: Dana Whitfield > Marcus Bell > Elena Sokolov > Tomas Ferreira > Hugo Marchetti is the deepest, at depth 4.

Guarding against a loop

The recursion stops because the org chart ends. Bad data can make it endless: if someone set Marcus Bell's manager to Hugo Marchetti, Hugo would lead back to Marcus, Marcus to Hugo's managers, and round again. With union all, every lap adds rows, and the query never finishes. A depth limit in the recursive part puts a ceiling on it:

with recursive org as (
  select employee_id, 1 as depth
  from employees
  where manager_id = 2

  union all

  select e.employee_id, org.depth + 1
  from employees as e
  join org on e.manager_id = org.employee_id
  where org.depth < 10
)
select depth, count(*) as employees
from org
group by depth
order by depth

On the real data the limit changes nothing (the deepest level under Marcus is 3), but on a looped copy it stops the query at depth 10 instead of running forever. If a guarded query returns rows at exactly the limit, treat that as a sign of a loop in the data, not a deep hierarchy.

What DuckDB refuses

• Forget the word recursive and the self-reference fails: Binder Error: Circular reference to CTE "org", use WITH RECURSIVE to use recursive CTEs.
• An order by inside the CTE is a Parser Error: ORDER BY in a recursive query is not allowed. Sort in the outer query instead.

WITH RECURSIVE works the same way in PostgreSQL, MySQL 8, and SQLite. Oracle leaves out the word and needs a column list after the CTE's name. SQL Server also leaves out the word recursive and caps the recursion at 100 levels unless you raise it with option (maxrecursion n).