How does a recursive CTE work, and how would you use one to walk an employee-manager hierarchy?
A recursive CTE has an anchor member that seeds the recursion and a recursive member that joins back to the CTE itself; the engine iterates until no new rows are produced. It is the standard SQL approach for querying trees and graphs such as org charts, bill-of-materials, and threaded comments.
How to think about it
What interviewers want to hear is that you understand the engine’s loop, not just the syntax. A recursive CTE isn’t magic — the database runs the recursive member over and over, each pass joining it against only the newly produced rows from the previous pass, until a pass yields nothing new.
The structure is two members joined by UNION ALL: an anchor that seeds the roots, and a recursive member that extends one level deeper by joining back to the CTE. Walking an org chart top-down looks like this:
WITH RECURSIVE org_tree AS (
SELECT employee_id, name, manager_id,
0 AS depth, name AS path
FROM employees
WHERE manager_id IS NULL -- anchor: the CEO (no manager)
UNION ALL
SELECT e.employee_id, e.name, e.manager_id,
ot.depth + 1, ot.path || ' > ' || e.name
FROM employees e
JOIN org_tree ot ON e.manager_id = ot.employee_id -- recursive: one level down
)
SELECT depth, name, path
FROM org_tree
ORDER BY path;A worked example — the org chart it produces
depth | name | path
------+----------------+------------------------------------------------------
0 | Alice (CEO) | Alice (CEO)
1 | Bob (VP Eng) | Alice (CEO) > Bob (VP Eng)
2 | Dave (Eng) | Alice (CEO) > Bob (VP Eng) > Dave (Eng)
3 | Heidi (Eng) | Alice (CEO) > Bob (VP Eng) > Dave (Eng) > Heidi (Eng)
2 | Eve (Eng) | Alice (CEO) > Bob (VP Eng) > Eve (Eng)
1 | Carol (VP Mkt) | Alice (CEO) > Carol (VP Mkt)
2 | Frank (Mkt) | Alice (CEO) > Carol (VP Mkt) > Frank (Mkt)
2 | Grace (Mkt) | Alice (CEO) > Carol (VP Mkt) > Grace (Mkt)Read the passes off the depth column. Pass 0 (the anchor) found Alice. Pass 1 found her direct reports, Bob and Carol. Pass 2 found their reports — Dave, Eve under Bob; Frank, Grace under Carol. Pass 3 reached Heidi under Dave, then a fourth pass found no new children and the loop stopped. Ordering by the accumulated path string renders it as a depth-first indented tree. Change the anchor to WHERE employee_id = 2 and you get just Bob’s subtree — the recursion mechanism is identical, only the seed changed.
Why each pass extends exactly one level
The recursive member can only see the CTE’s current working set — the rows the previous pass produced — never the full accumulated result. So each iteration joins the latest frontier to its children and adds exactly one new layer. The loop terminates naturally when that join returns nothing.