Detailed explanation
A recursive common table expression (CTE) lets a query repeatedly use previously produced rows to explore hierarchical or linked data. Typical uses include employee reporting structures, category trees, and graph traversal. It has two main parts: an anchor member that selects the starting rows, and a recursive member that references the CTE to find the next rows. These are commonly connected using UNION ALL. PostgreSQL and MySQL 8.0 use WITH RECURSIVE; SQL Server supports recursive CTEs but uses WITH without the RECURSIVE keyword.
For an employee hierarchy, write: WITH RECURSIVE org AS (SELECT id, name, manager_id FROM employees WHERE manager_id IS NULL UNION ALL SELECT e.id, e.name, e.manager_id FROM employees e JOIN org o ON e.manager_id = o.id) SELECT * FROM org; The anchor selects employees who have no manager. The recursive join finds employees whose manager is one of the employees discovered in the previous iteration. The final SELECT reads all accumulated rows. Both members must return the same number of columns in corresponding positions, with compatible data types; exact type-resolution rules vary by database.
Conceptually, the database evaluates the anchor first and places its result into a working set. It then runs the recursive member against that working set, collects the new rows, and repeats using those rows as the next working set. Processing stops when an iteration produces no rows. In this example, reaching employees with no direct reports ends each branch naturally, so termination does not require an explicit depth predicate. UNION ALL preserves duplicates and avoids duplicate-elimination work, but it does not prevent cycles.
Interviewers judge whether you can explain termination, traversal scope, and safety rather than merely recall syntax. Starting from manager_id IS NULL excludes disconnected components. For data that may contain cycles, track visited identifiers or use database-supported cycle detection; a depth limit is only a safeguard and may truncate valid results. Add a depth or path column when needed, and use an explicit ORDER BY because recursive evaluation does not guarantee presentation order. An index on employees(manager_id) can help the repeated child lookup.
Key points to remember
- Define an anchor for starting rows and a recursive member that references the CTE.
- Use UNION ALL to accumulate rows; align column counts and compatible types.
- Recursion stops when the recursive member returns no further rows.
- Handle possible cycles explicitly and choose starting rows carefully.
- Check dialect syntax: PostgreSQL and MySQL use WITH RECURSIVE; SQL Server uses WITH.
Common mistakes candidates make
- Reversing the join condition and traversing toward managers instead of direct reports.
- Assuming UNION ALL removes duplicates or protects against cyclic relationships.
- Assuming all employees are reachable from root rows or that output has an automatic hierarchy order.
Likely follow-up questions
How do you add a hierarchy level?
Add 0 AS depth to the anchor and o.depth + 1 AS depth to the recursive member. A predicate such as WHERE o.depth < 5 allows generated rows through depth 5, but excludes deeper descendants.
Can UNION replace UNION ALL to prevent infinite recursion?
Where the database permits it, UNION removes duplicate complete rows and can stop repeated visits when those rows remain identical. It is not general cycle protection: changing depth or path values makes revisits distinct.
How do you retrieve everyone reporting to one employee?
Change the anchor to WHERE id = :employee_id, using your database driver's parameter syntax. Keep the recursive join unchanged; the result includes the selected employee, so filter out depth 0 if you want only their direct and indirect reports.
Interview tip: Present the anchor–recursive member–termination structure first, then explain the employee join and mention cycle safety and dialect differences.