✏️ Explanatory Question
Level: Hard — Hierarchical data (org charts, categories, trees) is a classic hard problem; recursive CTEs are the clean solution.
Scenario: Your employees table stores a manager_id pointing to another employee (self-referencing). HR wants "the full reporting chain under the CEO" — every employee, at every level, no matter how deep. A regular JOIN cannot handle arbitrary depth. How do you traverse the tree?
A recursive CTE is a WITH RECURSIVE query that references itself, letting it walk a hierarchy level by level. It has two parts joined by UNION ALL: an anchor (the starting rows) and a recursive member (which repeatedly joins back to find the next level).
employees (self-referencing)| emp_id | name | manager_id |
|---|---|---|
| 1 | Ajay (CEO) | NULL |
| 2 | Rumman | 1 |
| 3 | Krushna | 1 |
| 4 | Swetha | 2 |
| 5 | Ritesh | 2 |
| 6 | Manjula | 4 |
WITH RECURSIVE org_chart AS (
-- ANCHOR: start at the top (the CEO, who has no manager)
SELECT emp_id, name, manager_id, 1 AS level,
CAST(name AS CHAR(200)) AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- RECURSIVE MEMBER: find each employee whose manager is already in the tree
SELECT e.emp_id, e.name, e.manager_id, oc.level + 1,
CONCAT(oc.path, ' > ', e.name)
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.emp_id
)
SELECT emp_id, name, level, path
FROM org_chart
ORDER BY level, emp_id;| emp_id | name | level | path |
|---|---|---|---|
| 1 | Ajay (CEO) | 1 | Ajay (CEO) |
| 2 | Rumman | 2 | Ajay (CEO) > Rumman |
| 3 | Krushna | 2 | Ajay (CEO) > Krushna |
| 4 | Swetha | 3 | Ajay (CEO) > Rumman > Swetha |
| 5 | Ritesh | 3 | Ajay (CEO) > Rumman > Ritesh |
| 6 | Manjula | 4 | Ajay (CEO) > Rumman > Swetha > Manjula |
How it executes: The anchor runs once (the CEO). Then the recursive member runs repeatedly — each pass finds employees reporting to those found in the previous pass — until no new rows appear. The level counter and path string are built up as it descends.
-- Find all managers ABOVE a specific employee (Manjula, id 6)
WITH RECURSIVE mgr_chain AS (
SELECT emp_id, name, manager_id
FROM employees WHERE emp_id = 6 -- anchor: the employee
UNION ALL
SELECT e.emp_id, e.name, e.manager_id
FROM employees e
JOIN mgr_chain mc ON e.emp_id = mc.manager_id -- climb to the manager
)
SELECT * FROM mgr_chain;
-- Manjula -> Swetha -> Rumman -> Ajay (the chain upward)-- How many people report to Rumman, at any depth below him?
WITH RECURSIVE subtree AS (
SELECT emp_id FROM employees WHERE emp_id = 2 -- start at Rumman
UNION ALL
SELECT e.emp_id FROM employees e
JOIN subtree s ON e.manager_id = s.emp_id
)
SELECT COUNT(*) - 1 AS total_reports FROM subtree; -- minus Rumman himself Guard against infinite loops: If the data has a cycle (A manages B, B manages A by mistake), a recursive CTE loops forever. MySQL caps recursion at cte_max_recursion_depth (default 1000) and errors out. For safety on messy data, add a WHERE level < 100 guard or track visited nodes in the path.
Interviewer follow-up: "Recursive CTEs get slow on deep/wide trees — what are the alternatives?" → For read-heavy hierarchies, consider a materialized path (store the full path like /1/2/4/ and query with LIKE '/1/2/%') or the nested set model (store left/right bounds). Both trade write complexity for very fast subtree reads — better than recursion when the tree is queried far more than it changes.