Hierarchical data appears everywhere: employees report to managers, comments reply to other comments, folders contain folders, and categories form parent-child trees.

The table structure is usually simple. The query is the hard part.

A normal join follows a fixed number of relationships. A recursive common table expression, or recursive CTE, can follow the same relationship repeatedly until there are no more rows to visit.

The key mental model is: start with an anchor set, repeatedly derive the next set from the previous one, then return the accumulated rows.

Start with an adjacency-list table

A common representation stores each row’s parent identifier in the same table:

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    manager_id INTEGER REFERENCES employees(id)
);

This is called an adjacency list. Each row points to its immediate parent.

Example data might look like:

id | name | manager_id
---+------+-----------
1  | Maya | NULL
2  | Noah | 1
3  | Iris | 1
4  | Omar | 2
5  | Lina | 2
6  | Kai  | 4

The direct relationships are easy to query. To find Noah’s direct reports:

SELECT id, name
FROM employees
WHERE manager_id = 2;

That returns Omar and Lina.

The harder question is: who is anywhere under Noah, including reports several levels deep?

A recursive CTE has an anchor and a recursive member

The smallest useful recursive query is:

WITH RECURSIVE team(id, name, manager_id, depth) AS (
    SELECT id, name, manager_id, 0
    FROM employees
    WHERE id = 2

    UNION ALL

    SELECT e.id, e.name, e.manager_id, t.depth + 1
    FROM employees AS e
    JOIN team AS t
      ON e.manager_id = t.id
)
SELECT id, name, depth
FROM team;

There are two parts inside team.

The first SELECT is the anchor member. It does not reference the CTE. It chooses the rows where traversal begins.

The second SELECT is the recursive member. It references team, finds rows related to the current rows, and feeds those new rows back into the CTE.

UNION ALL combines the anchor output with rows produced by recursive steps.

For Noah, the result contains:

id | name | depth
---+------+------
2  | Noah | 0
4  | Omar | 1
5  | Lina | 1
6  | Kai  | 2

Depth 0 is the starting employee, depth 1 is a direct report, and depth 2 is a report of a report.

Think in iterations, not function calls

A recursive CTE is written recursively, but you do not need to imagine SQL calling a function on each row.

A practical mental model is iterative:

  1. Run the anchor query.
  2. Treat those rows as the current working set.
  3. Run the recursive member using that working set.
  4. Add newly produced rows to the result.
  5. Repeat with the new working set.
  6. Stop when the recursive member produces no more rows.

For the employee example:

anchor:     Noah
step 1:     Omar, Lina
step 2:     Kai
step 3:     no rows
stop

The database engine controls the actual execution strategy. The SQL language defines the recursive relationship; implementation details such as queues, temporary storage, or materialization can differ between systems.

That distinction matters when reasoning about correctness versus performance. The anchor and recursive rules are part of the query’s semantics. The engine’s internal mechanism is not a portable guarantee.

Traverse upward by reversing the join

To find an employee’s management chain, keep the same structure but reverse the relationship:

WITH RECURSIVE chain(id, name, manager_id, depth) AS (
    SELECT id, name, manager_id, 0
    FROM employees
    WHERE id = 6

    UNION ALL

    SELECT e.id, e.name, e.manager_id, c.depth + 1
    FROM employees AS e
    JOIN chain AS c
      ON e.id = c.manager_id
)
SELECT name, depth
FROM chain;

Starting from Kai, this produces Kai, Omar, Noah, then Maya.

The difference between traversing down and traversing up is only the join condition:

descendants: child.manager_id = current.id
ancestors:   parent.id = current.manager_id

This is a reusable pattern for folders, categories, comments, dependency chains, and other self-referencing tables.

Add path data only when you need it

Depth is cheap to reason about because it is just one integer carried into each recursive step.

A human-readable path can also be useful:

WITH RECURSIVE team(id, name, manager_id, depth, path) AS (
    SELECT id, name, manager_id, 0, name
    FROM employees
    WHERE id = 1

    UNION ALL

    SELECT
        e.id,
        e.name,
        e.manager_id,
        t.depth + 1,
        t.path || ' > ' || e.name
    FROM employees AS e
    JOIN team AS t
      ON e.manager_id = t.id
)
SELECT name, depth, path
FROM team;

String concatenation syntax varies between SQL products, so treat the path expression as dialect-specific even though the recursive structure is broadly portable.

Paths are useful for display, debugging, and some forms of cycle detection. They also increase the amount of data carried through every recursive row. Avoid building large path strings if the caller only needs identifiers and depth.

Ordering the final rows is separate from traversal

Do not assume recursive output arrives in tree order.

Without an outer ORDER BY, SQL does not guarantee a useful presentation order. A query may happen to look breadth-first or depth-first on one engine and change after a plan change, data change, or database upgrade.

If depth order is sufficient, request it explicitly:

SELECT id, name, depth
FROM team
ORDER BY depth, id;

That groups the starting row first, then depth 1, then depth 2, while using id as a deterministic tiebreaker.

If you need exact depth-first tree presentation, carry a sortable path or use a database feature designed for search ordering. The available syntax differs across database systems.

The important rule is portable: traversal semantics and output ordering are different concerns.

Cycles can make recursion unsafe

A hierarchy is expected to be acyclic. Real data may not honor that assumption.

Imagine these relationships:

A reports to B
B reports to C
C reports to A

A recursive query using UNION ALL can keep rediscovering the same nodes. Some database engines stop at a recursion limit; others may continue until another resource limit is reached.

Do not rely on an engine limit as your primary cycle policy.

Prevent invalid cycles when possible

The strongest solution is to stop cycles from entering the data model.

A simple foreign key ensures that a parent identifier references a real row, but it does not by itself prove that the whole graph is acyclic. Enforcing global acyclicity may require application logic, triggers, deferred validation, or database-specific features.

If cycles are invalid in your domain, make cycle prevention part of the write path rather than expecting every read query to defend against arbitrary graph corruption.

Add a depth guard when a natural bound exists

If a legitimate hierarchy can never exceed a known depth, add that rule:

WITH RECURSIVE team(id, name, manager_id, depth) AS (
    SELECT id, name, manager_id, 0
    FROM employees
    WHERE id = 1

    UNION ALL

    SELECT e.id, e.name, e.manager_id, t.depth + 1
    FROM employees AS e
    JOIN team AS t
      ON e.manager_id = t.id
    WHERE t.depth < 20
)
SELECT id, name, depth
FROM team;

This is a safety bound, not full cycle detection. It prevents unbounded recursion but can still return repeated nodes before the bound is reached.

Choose a limit only when the domain provides a defensible maximum. An arbitrary small limit can silently truncate legitimate results.

UNION and UNION ALL have different costs and semantics

Developers sometimes replace UNION ALL with UNION to suppress repeated rows:

anchor
UNION
recursive_member

UNION removes duplicate rows. That can help terminate some graph traversals when revisiting a node produces an identical row.

But there are two important trade-offs.

First, deduplication has a cost. The engine must track enough information to determine whether a row has already appeared.

Second, duplicate elimination only works on the complete CTE row. If you carry changing data such as depth or a growing path, revisiting the same node can still produce a different row:

(id = 5, depth = 2)
(id = 5, depth = 5)

Those are not duplicates.

Use UNION ALL for well-formed trees where each node is reached once through the chosen direction. Use explicit cycle handling for general graphs or data that may contain cycles.

Index the relationship used by the recursive join

A recursive query can execute the same relationship lookup many times.

For descendant traversal, the recursive member searches by manager_id:

JOIN team AS t
  ON e.manager_id = t.id

An index on the child-side relationship is therefore commonly useful:

CREATE INDEX employees_manager_id_idx
ON employees (manager_id);

Without a suitable index, the database may need to repeatedly scan a large portion of the table while expanding the hierarchy.

For upward traversal, e.id = c.manager_id normally uses the primary-key index on id.

Indexes do not make recursion automatically fast. Cost also depends on tree size, branching factor, selected columns, duplicate handling, filters, and the optimizer. Confirm important queries with the target database’s execution plan and realistic data.

Filter in the recursive member when the rule affects expansion

Where you place a predicate can change the meaning of traversal.

Suppose disabled employees should be excluded from the final display, but their reports should still be reachable. Filtering only in the outer query can preserve traversal through disabled managers:

SELECT id, name, depth
FROM team
WHERE status <> 'disabled';

By contrast, adding the condition inside the recursive member can stop expansion through those rows, depending on how the query is written.

This distinction is important:

  • outer filters decide which accumulated rows are returned;
  • recursive filters can decide which rows are allowed to generate further rows.

When a condition belongs to the recursive member, make sure you intend it to affect traversal, not merely presentation.

Recursive CTEs are not always the best storage model

Adjacency lists are simple to write and flexible to update. Recursive CTEs make them practical for many hierarchies.

They are not ideal for every workload.

If an application performs very frequent deep ancestor or descendant queries over a mostly static hierarchy, other models may be worth considering, such as closure tables, materialized paths, or database-specific hierarchical types.

Those alternatives trade simpler reads for additional storage, more complex writes, or database-specific behavior.

A recursive CTE is a strong default when:

  • the data naturally stores immediate parent-child relationships;
  • hierarchy depth is moderate;
  • writes should remain straightforward;
  • the database supports recursive CTEs;
  • queries need dynamic traversal rather than precomputed ancestry.

A simpler fixed join is better when the maximum depth is small and structurally fixed. Precomputed hierarchy models can be better when deep traversal dominates the workload and write complexity is acceptable.

Common mistakes

Forgetting the stopping condition

The recursive member must eventually stop producing rows for valid input. In a tree, reaching leaves naturally does that. In graphs, cycles require explicit thought.

Assuming output order

Recursive output has no useful guaranteed order unless you request one. Add an outer ORDER BY or a deliberate search-order mechanism.

Using UNION as universal cycle detection

UNION removes identical rows, not identical logical nodes when other projected columns differ.

Carrying too much state

Large path strings or unused columns make every recursive row wider. Carry only the state needed for traversal, filtering, cycle handling, or final output.

Missing the relationship index

Repeated child lookups can become expensive without an index on the parent-reference column.

Filtering at the wrong stage

A predicate in the recursive member can prevent descendants from ever being reached. A predicate in the outer query only changes which accumulated rows are returned.

Conclusion

Recursive SQL CTEs are easiest to understand as repeated set expansion.

The anchor member chooses where traversal starts. The recursive member describes one relationship step. Each iteration feeds newly found rows into the next step until no more rows are produced.

From that model, the practical rules follow naturally: carry depth when it helps, order final results explicitly, treat cycles as a correctness concern, use UNION ALL when duplicates are not a problem, index the relationship followed by recursion, and place filters according to whether they should affect traversal or only output.

Once the anchor-and-step model is clear, hierarchical queries stop looking like special SQL syntax and become a reusable way to walk structured relationships.

Database SQL Queries Backend