Recursive Employee Tree

Hook: Interviewers love this one because it checks whether you can turn a simple manager-to-employee table into a full org chart, not just one level of reporting.

Question: How do I return an employee tree, including each person’s chain of managers and subordinates, using SQL?

Answer: Use a recursive CTE, which is a common table expression that refers to itself. Start with the top-level employees as the anchor rows, then repeatedly join each found manager to their direct reports until no new rows appear. This gives you the whole hierarchy with a depth level and, if you want, a readable path.

Interview-Ready Answer: I’d use a recursive CTE. First I select the root employees where manager_id is NULL, then I union a recursive step that joins each row to its direct reports using employee_id = manager_id. I usually carry a depth column and a path string so the result is easy to sort and read. The important detail is UNION ALL, because I want every discovered row and I do not want duplicate elimination unless I truly need it.

What it is

Detailed Explanation: A recursive CTE is the SQL version of “keep walking downward one level at a time.” The first query gets the starting point, and the second query uses the rows from the first pass to find the next pass. In org-chart terms, the first pass finds the CEO or department heads, and later passes find their reports, then the reports of those reports.

How it works under the hood

1. The anchor member runs first. This is the non-recursive part, usually the root employees where manager_id IS NULL.
2. The database stores those rows in a working set, which is just an internal temporary result it can feed back into the query.
3. The recursive member runs next. It joins the base table to the rows already found, usually with employees.manager_id = tree.employee_id.
4. Every pass adds the next generation: direct reports, then indirect reports, then deeper levels.
5. The engine repeats until a pass returns no new rows. That stopping condition is what makes recursion finish.
6. If you include a depth column, you can see the level number; if you include a path, you can sort or display the tree more clearly.

Why UNION ALL matters

For an employee tree, UNION ALL is the normal choice because each person should appear once if your data is clean. UNION does duplicate checking, which adds cost and can change results if your query shape is not careful.

When and why to use it

• Use it when the depth is not known ahead of time.
• Use it for org charts, category trees, bill of materials, folder trees, or permission inheritance.
• Use a plain self-join only when the depth is fixed and small, like exactly two levels.

Performance and edge cases

With an index on manager_id, the recursive join can be very efficient because each level can find children quickly. Without that index, the database may scan the employee table again and again, and the query can drift toward quadratic behavior on big data. In practical terms, a 10,000-row org chart is usually fine with indexing; 100,000 rows is still common, but you should test it and watch the execution plan.

Also watch for dialect differences: SQL Server has a default recursion cap of 100 levels unless you change it, while PostgreSQL and SQLite allow recursive CTEs without that same small built-in cap. That matters if you have very deep hierarchies.

• Multiple roots: normal; the anchor can return all top-level employees.
• Orphans: rows whose manager does not exist; they will not show in the tree unless you query them separately.
• Cycles: dangerous; if A manages B and B manages A, recursion can loop until the database stops it. Real systems prevent this with constraints or by tracking visited nodes.

Memory of the mechanism: think of a flashlight shining from the top of the org chart downward, one layer at a time. The light starts at the roots, then spreads to children, then to grandchildren.

Real-World Example: Imagine an internal HR directory or approval system. When a manager opens a team page, the app needs the manager, all direct reports, all indirect reports, and sometimes the full chain above a person for approvals. A recursive CTE is a natural fit because the depth is unknown and changes as the company grows.

What goes wrong when people misunderstand it? A common bug is writing only one self-join, which returns direct reports but silently drops everyone deeper in the tree. In production, that looks like incomplete org charts, missing approvers, and access rules that seem to skip entire teams. Another failure mode is bad data with a cycle; then the query can become slow or hit the recursion limit, and logs may show repeated recursion warnings or timeouts while users see spinners or empty pages.

That is why interviewers like this question: it tests both SQL skill and whether you think about data quality, indexing, and termination conditions, not just syntax.

Follow-up & Tricky Questions:

• How do I return only one manager’s subtree? Anchor the CTE on that manager instead of all roots. For example, start from employee_id = 2 and then recurse downward from there.
• How do I include a depth or level column? Add 0 AS depth in the anchor member and depth + 1 in the recursive member. That makes it easy to indent, sort, or limit levels.
• How do I build a readable path? Concatenate each employee name or id as you recurse, like CEO > VP Engineering > Engineering Manager. That helps with debugging and stable ordering.
• How do I prevent cycles? Enforce data integrity if you can, and in more advanced cases keep a visited-path check so the same employee cannot appear twice in one chain. Cycles are the main reason a seemingly simple tree query becomes dangerous.
• Why is UNION ALL preferred over UNION? Because a tree is supposed to emit every discovered row, and duplicate elimination is wasted work unless you are intentionally deduplicating a graph.
• Tricky: What if there are multiple top-level managers? That is still fine. The anchor member can return several roots, and the result is really a forest, not a single tree.
• Tricky: What if manager_id points to a missing employee? That employee becomes an orphan and will not appear in the recursive tree. You need a separate query or a data-fix step to surface the problem.
• Tricky: Does the output order automatically match the tree order? No. The engine evaluates levels recursively, but you should still add an explicit ORDER BY using depth or path if presentation matters.

Common Mistakes:

• Using only one self-join. Correction: that only gives one level of reports; use a recursive CTE for unknown depth.
• Forgetting the anchor member. Correction: recursion needs a starting row set, usually the roots or a chosen manager.
• Using UNION when UNION ALL is enough. Correction: UNION ALL is faster and usually the right choice for trees.
• Ignoring bad data. Correction: check for orphans and cycles, because they can hide rows or make the query blow up.

Memory Hook: Roots first, then one branch at a time. Picture a tree drawn by dropping a pebble at the top; each ripple is the next level of reports.

Cheat Sheet:

• WITH RECURSIVE = named query that can refer to itself.
• Anchor member = starting rows.
• Recursive member = self-join to the rows already found.
• Carry depth and path for readability.
• Prefer UNION ALL for hierarchy traversal.
• Index manager_id for speed.

Practice Tasks:

• Return only the subtree under one manager.
• Add a level column and sort the tree by level then name.
• Write a query that finds orphan employees whose manager is missing.