Employee Hierarchy

Hook: An employee tree is just a row that keeps pointing to its parent — like following breadcrumbs from the CEO down to every report.

Question: Given a table where each employee has a manager_id, how do I return the full employee hierarchy in SQL?

Answer: Use a recursive CTE — a Common Table Expression that can reference itself. The first part picks the top employee(s), usually the CEO, and the recursive part keeps joining children to the rows already found, level by level. Add a depth column so you can see how far each person is from the root.

Interview-Ready Answer: I’d use a recursive CTE. I start with the root rows where manager_id IS NULL, then I recursively join employees to their manager to pull in direct reports until no more rows are found. I usually include a level column and an index on manager_id because that keeps the traversal fast and makes the output easy to read.

What this problem is really asking

You are usually given an adjacency list model: each employee row stores its parent, which is the manager. That is simple to write but awkward to query if you want the full tree, because one row only knows its immediate boss, not all of its ancestors or descendants.

How a recursive CTE works under the hood

1. Find the roots. The anchor member (the starting query) returns the top-level employee(s), often those with manager_id IS NULL.
2. Seed extra columns. We often add level (depth from the root) and sometimes path (a readable trail like CEO > VP > Manager).
3. Join one level deeper. The recursive member joins employees e to the rows already found on e.manager_id = current.employee_id.
4. Repeat in waves. The database takes the rows from the previous wave, feeds them into the recursive part, and keeps going until a pass produces zero new rows.
5. Combine everything. UNION ALL stacks each wave together. We usually prefer UNION ALL because it avoids expensive de-duplication.
6. Sort for presentation. SQL does not guarantee order unless you ask for it, so we sort by path, level, or both.

When and why to use it

• Org charts: who reports to whom.
• Approval chains: manager approval, director approval, finance approval.
• Category trees: departments, product categories, file folders.
• Bill of materials: components made of sub-components.

It is the right tool when the hierarchy depth is not fixed. If you only ever need two or three levels, a few self-joins might be enough. If the depth changes over time, recursion is much cleaner and safer.

Recursive CTE vs other options

Closure table means a separate table that stores every ancestor-descendant pair ahead of time. It is great when reads are frequent and writes are rare, but inserts and moves become more complicated.

Performance, limits, and complexity

With an index on manager_id, each recursive step can find children quickly, so the traversal is usually close to O(n) for n employees, plus any sort cost at the end. Without that index, the engine may rescan the employee table many times, and the query can drift toward O(n^2) behavior on large data.

Real-world limits matter too: SQL Server defaults to MAXRECURSION 100 unless you override it, and MySQL 8 commonly uses cte_max_recursion_depth = 1000. Deep hierarchies are rare in normal orgs, but bad data or a very large enterprise can hit those caps.

Edge cases interviewers care about

• Multiple roots: A table can have more than one top-level tree, and the recursive CTE will return all of them.
• Orphan rows: If an employee points to a manager that does not exist, that row will not appear in a root-based tree unless you query for it separately.
• Cycles: If employee A points to B and B points back to A, recursion can loop forever unless the database cap stops it. A simple CHECK (employee_id <> manager_id) prevents only direct self-reference, not longer cycles.
• Ordering: A tree is not automatically printed in a nice visual order. You must sort it yourself.

Memory rule: think of the CEO as the trunk of a tree. The recursive CTE grows one branch level at a time until there are no more leaves to add.

Real-world story

Imagine a payroll and expense-approval service for a SaaS company. Every expense request needs the employee’s manager, and sometimes the manager’s manager, to approve it. The app uses an employee hierarchy query to build the approval chain and the org chart shown in the admin dashboard.

One team shipped a fixed three-level self-join because it seemed simpler. It worked for small departments, but a new division had seven layers. Requests from deeper employees skipped the last approver, so approvals got stuck in pending state and the finance team could not close the books.

Then a second bug appeared: a bad row made an employee point to themselves as manager. The recursive query never found a clean stopping point, so the database hit its recursion limit. In production this showed up as slow dashboard loads, timeout errors, and logs mentioning recursion depth being exhausted. Users saw missing org-chart nodes, and support saw a wave of tickets about approval chains disappearing.

The lesson is simple: recursion is not just a fancy SQL trick. It is the difference between a query that grows with the business and one that breaks the first time the org chart changes shape.

Follow-up & Tricky Questions:

• How do you return only one employee’s reporting chain? Start from that employee in the anchor member, then recurse upward with m.employee_id = c.manager_id. That gives you the path from the person to the root.
• How do you show the level of each employee? Add a numeric column like level or depth and increment it in the recursive part. That makes indenting, sorting, and filtering much easier.
• How do you stop infinite recursion from bad data? Prefer a real constraint or clean-up rule, and use your database’s recursion limit as a safety net. A self-check like employee_id <> manager_id only blocks direct self-loops, not longer cycles.
• What index should I add? Index manager_id, because the recursive member repeatedly looks up children by manager. In many org queries that is the single most important performance improvement.
• Why might my output miss some employees? If you start only from manager_id IS NULL, orphan rows and disconnected subtrees will not appear. That usually means the query is fine and the data is dirty.
• Why use UNION ALL instead of UNION? UNION ALL is faster because it does not remove duplicates at every step. UNION can hide bad data and adds extra work.
• Does WHERE level < 3 in the final query stop recursion early? No — it only filters the output after the recursion has already happened. To limit work, put the depth condition inside the recursive member.
• What if there are multiple CEOs or roots? That is okay: the anchor member can return several rows, and the recursive CTE will grow a forest, not just one tree.

Common Mistakes:

• Using a fixed number of self-joins. That works only for a known depth. Correction: use a recursive CTE when the hierarchy can grow.
• Forgetting the base case. If you do not select the root rows first, recursion has nothing to start from. Correction: anchor on manager_id IS NULL or on the specific employee you care about.
• Sorting without a plan. A tree can look random if you do not add ORDER BY. Correction: sort by path or by level plus a stable sibling key.
• Ignoring bad data. Orphans and cycles can make the result incomplete or unsafe. Correction: validate rows and add the right index and constraints.

Memory Hook: Picture a flashlight shining from the CEO downward. Each recursive pass lights up the next row of the org chart until there is nothing left in the dark.

Cheat Sheet:

• Start with a root row in the anchor member.
• Join children in the recursive member.
• Use UNION ALL, not UNION, unless you truly need de-duplication.
• Add level and maybe path for readability.
• Index manager_id for speed.
• Watch for orphans, multiple roots, and cycles.

Practice Tasks:

• Change the sample query so it returns only employees under manager 2.
• Add a path column that shows names from the CEO to each employee and order by it.
• Write a query that finds employees who have more than one direct report.