Traversing Hierarchical Data Without Leaving SQL
Relational databases were designed for flat, tabular data — rows and columns with well-defined relationships. But the data we actually work with rarely cooperates. Org charts nest managers under executives under directors. Bill-of-materials structures chain components through multiple levels. Account hierarchies in financial systems can run six or seven levels deep. The moment you encounter this kind of data, the temptation is to pull it into application code and loop through it. Recursive CTEs exist precisely so you don’t have to.
A recursive CTE lets SQL traverse hierarchical or graph-like data within a single query, following relationships from node to node until a termination condition is met. Understanding how they work mechanically not just how to copy-paste one unlocks a whole class of analytical problems that flat SQL can’t touch.
What Makes a CTE “Recursive”
A standard Common Table Expression (CTE) is syntactic sugar: it names a result set that can be referenced once in the query that follows. A recursive CTE does something fundamentally different. It defines a result set that references itself the output of one iteration becomes the input to the next.
Every recursive CTE has exactly two parts, joined by UNION ALL:
The anchor member a base query with no self-reference. This returns the starting rows (the roots, or the seeds of your traversal).
The recursive member a query that joins back to the CTE itself. This is what “walks” the tree, one level at a time.
The SQL engine evaluates the anchor first, producing an initial working table. It then runs the recursive member against that working table. Any rows produced become the new working table for the next iteration. This repeats until the recursive member returns zero rows that’s the natural termination condition. Most databases also enforce a maximum recursion depth (typically 100) as a safeguard against infinite loops.
WITH RECURSIVE cte_name AS (
-- Anchor: starting point
SELECT ...
UNION ALL
-- Recursive member: join cte_name back to itself
SELECT ...
FROM source_table
JOIN cte_name ON ...
)
SELECT * FROM cte_name;
The WITH RECURSIVE keyword is ANSI SQL standard. Some databases (SQL Server, for instance) accept plain WITH even for recursive CTEs the recursion is inferred from the self-reference.
Walking an Employee Hierarchy
Consider a table storing an organizational chart. Each row is an employee; the manager_id column points to the employee_id of their direct manager. The CEO has no manager, so their manager_id is NULL.
CREATE TABLE employees (
employee_id INT,
name VARCHAR(100),
manager_id INT,
title VARCHAR(100)
);
Now suppose you want to retrieve the entire reporting chain starting from the CEO, showing each person’s depth in the hierarchy. This is impossible with a single flat query you don’t know in advance how many levels exist. A recursive CTE handles it cleanly:
WITH RECURSIVE org_hierarchy AS (
-- Anchor: start at the top (no manager)
SELECT
employee_id,
name,
title,
manager_id,
0 AS depth,
CAST(name AS VARCHAR(500)) AS path
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive: find each person's direct reports
SELECT
e.employee_id,
e.name,
e.title,
e.manager_id,
h.depth + 1,
CAST(h.path || ' > ' || e.name AS VARCHAR(500))
FROM employees e
JOIN org_hierarchy h ON e.manager_id = h.employee_id
)
SELECT
depth,
REPEAT(' ', depth) || name AS indented_name,
title,
path
FROM org_hierarchy
ORDER BY path;
Expected output:
depth | indented_name | title | path
0 | Sarah Chen | CEO | Sarah Chen
1 | Marcus Webb | VP of Engineering | Sarah Chen > Marcus Webb
1 | Priya Nair | VP of Finance | Sarah Chen > Priya Nair
2 | Tom Okafor | Engineering Manager | Sarah Chen > Marcus Webb > Tom Okafor
2 | Leila Soto | Engineering Manager | Sarah Chen > Marcus Webb > Leila Soto
3 | Dan Krause | Senior Engineer | Sarah Chen > Marcus Webb > Tom Okafor > Dan Krause
3 | Yuki Tanaka | Engineer | Sarah Chen > Marcus Webb > Tom Okafor > Yuki Tanaka
The path column is doing real analytical work here it encodes the full ancestry of each node as a string, built incrementally by concatenating the parent’s path with the child’s name at each iteration. The depth column counts how many recursive steps it took to reach each row from the anchor.
Traversing in Reverse: Upward Path Queries
Tree traversal doesn’t always go downward. An equally common use case is finding the full chain of command above a specific node say, a support ticket escalation system where you need to notify every manager up the chain.
Starting from a leaf node and walking upward toward the root requires only a small logical flip: the anchor becomes the target node, and the recursive join follows employee_id upward to manager_id instead of downward.
WITH RECURSIVE upward_chain AS (
-- Anchor: start at the specific employee
SELECT
employee_id,
name,
title,
manager_id,
0 AS steps_above
FROM employees
WHERE employee_id = 7 -- Yuki Tanaka
UNION ALL
-- Recursive: climb to each manager
SELECT
e.employee_id,
e.name,
e.title,
e.manager_id,
u.steps_above + 1
FROM employees e
JOIN upward_chain u ON e.employee_id = u.manager_id
)
SELECT
steps_above,
name,
title
FROM upward_chain
ORDER BY steps_above;
Expected output:
steps_above | name | title
0 | Yuki Tanaka | Engineer
1 | Tom Okafor | Engineering Manager
2 | Marcus Webb | VP of Engineering
3 | Sarah Chen | CEO
This query terminates naturally because eventually manager_id returns NULL there’s no matching row in employees, so the recursive member produces zero rows and execution stops. No WHERE clause needed to halt the recursion; the data structure itself provides the exit condition.
In a real-world escalation system, this result set could feed directly into a notification query, a permission-check join, or an audit trail. The full path is computed in SQL, without any round-trips to application code.
Controlling Depth and Preventing Infinite Loops
Hierarchies aren’t always clean. Real data contains cycles an employee record mistakenly set as its own manager, or a graph where A references B and B references A. Left unguarded, these turn a recursive CTE into an infinite loop (until the database enforces its depth limit and throws an error).
The standard defense is a depth counter combined with an explicit WHERE clause in the recursive member:
WITH RECURSIVE safe_traversal AS (
SELECT
employee_id,
name,
manager_id,
1 AS depth,
ARRAY[employee_id] AS visited -- PostgreSQL syntax for cycle detection
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT
e.employee_id,
e.name,
e.manager_id,
s.depth + 1,
s.visited || e.employee_id
FROM employees e
JOIN safe_traversal s ON e.manager_id = s.employee_id
WHERE s.depth < 10 -- hard depth cap
AND NOT (e.employee_id = ANY(s.visited)) -- cycle guard
)
SELECT employee_id, name, depth
FROM safe_traversal;
The visited array accumulates every employee_id seen along the path. Before adding a new row, the NOT ANY check confirms the node hasn’t already appeared in the current traversal path. If it has, that branch is pruned.
For databases without array types (standard SQL), depth-capping alone is the practical fallback:
WHERE s.depth < 10
Expected output (with safe_traversal on the original dataset):
employee_id | name | depth
1 | Sarah Chen | 1
2 | Marcus Webb | 2
3 | Priya Nair | 2
4 | Tom Okafor | 3
5 | Leila Soto | 3
6 | Dan Krause | 4
7 | Yuki Tanaka | 4
The right cap depends on your domain. A corporate hierarchy rarely exceeds 8–10 levels; a general graph might warrant a higher threshold or a more sophisticated visited-node tracking mechanism.
Recursive CTEs are one of the clearest examples of SQL doing something that feels like it shouldn’t be possible inside a declarative language. The iterative expansion anchor seeds the result, recursive member extends it, engine repeats until empty maps directly onto the mental model of walking a tree or following a chain.
What I find most valuable about mastering recursive CTEs isn’t just the syntax; it’s the shift in problem-framing. Once you recognize “ this is a traversal problem,” the solution pattern follows naturally. Hierarchical data isn’t a SQL limitation to work around it’s just another query shape.
The practical range of applications extends well beyond org charts: bill-of-materials explosions, account inheritance trees, dependency graphs, network path discovery, sequence gap detection. The moment your data has parent-child relationships at arbitrary depth, a recursive CTE is almost always the cleanest tool.
Recursive CTEs was originally published in Data Decoded on Medium, where people are continuing the conversation by highlighting and responding to this story.