SQL recursive CTEs for an organization hierarchy

A recursive common table expression walks relationships repeatedly: start with an anchor row, find its children, then find their children until no further eligible rows remain. An employee-to-manager table is a useful example because a single join reaches only one management level.

The query's termination and data-quality rules are part of the solution. A traversal can finish successfully while omitting a disconnected employee or silently stopping at a depth limit.

Define the hierarchy's assumptions

The synthetic advanced SQL lab contains four employees. S1 is the root lead; S2 and S4 report to S1; S3 reports to S2. Each employee has at most one manager and a unique employee_id. The exercise assumes a rooted tree, not a matrix organization with several reporting relationships.

WITH RECURSIVE hierarchy(employee_id, manager_id, name, depth, path) AS (
    SELECT employee_id, manager_id, name, 0, '|' || employee_id || '|'
    FROM staff WHERE employee_id = 'S1'
    UNION ALL
    SELECT s.employee_id, s.manager_id, s.name, h.depth + 1,
           h.path || s.employee_id || '|'
    FROM staff AS s
    JOIN hierarchy AS h ON s.manager_id = h.employee_id
    WHERE h.depth < 20
      AND instr(h.path, '|' || s.employee_id || '|') = 0
)
SELECT employee_id, manager_id, name, depth, path
FROM hierarchy ORDER BY path;

The output contains S1 at depth 0, S2 at 1, S3 at 2 and S4 at 1. The paths are respectively |S1|, |S1|S2|, |S1|S2|S3| and |S1|S4|. Lexicographic path sorting gives a convenient display for these simple IDs; it is not a universal business sort order.

The path check prevents revisiting an employee already in the current ancestry. Delimiters keep S1 from accidentally matching S10. This representation assumes identifiers cannot contain the delimiter. More general systems need an unambiguous path representation appropriate to their database.

The anchor and recursive parts follow SQLite's recursive CTE rules.

Understand what the depth guard does

The condition h.depth < 20 permits children through depth 20 and stops expansion afterward. It is an emergency bound for this exercise, not proof that the hierarchy is valid. A legitimate organization with greater depth would be truncated.

If any returned node reaches the limit and still has children, expose a truncation exception. Likewise, stopping a cycle prevents an endless traversal but does not repair the bad management relationship. Record or separately detect the offending edge instead of presenting a clean report with the problem hidden.

Find missing managers independently

SELECT s.employee_id, s.manager_id
FROM staff AS s
LEFT JOIN staff AS m ON m.employee_id = s.manager_id
WHERE s.manager_id IS NOT NULL AND m.employee_id IS NULL;

The fixture returns no rows. If S3's manager were S99, this check would expose it. Without the check, a root-based traversal would simply omit S3 because no path connects it to S1.

Also compare the reachable employee set with the intended reporting population. An employee in another legitimate root belongs to a separate tree, while an isolated cycle has no root at all. A root-only traversal cannot discover a disconnected cycle merely by watching its own visited paths.

For a whole-organization report, validate the expected roots and traverse all of them, carrying a root identifier. Decide whether contractors, inactive employees and historical reporting lines belong in scope.

Keep hierarchy time separate from transaction time

Today's reporting chain may differ from the chain in force when a sale or performance review occurred. If the business asks for historical manager attribution, use effective-dated relationships and select the correct snapshot before recursion. Joining historical facts to today's hierarchy can reassign past results without changing any transaction.

For matrix organizations, model relationships as edges rather than forcing several managers into one field. Multiple valid paths may then reach the same employee. Distinguish path counts from unique employee counts before rolling up headcount or value.

Exercise: introduce a missing manager and confirm the independent exception query detects it. Then create a disconnected two-person cycle. Explain why the root traversal alone cannot establish that every employee is reachable and valid.

NeuraPath's Data Analytics with Generative AI course provides a path from SQL syntax to defensible organizational reporting. A good recursive-query project includes the traversal, structural checks and a clear rule for historical attribution.

Continue learning

This article is part of the Advanced SQL and analytical patterns sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Reconcile two systems with a full outer join.
• Continue with Read EXPLAIN before adding an index.