Lesson 13 • Advanced Track

Recursive CTEs: Hierarchies, Graphs & Trees

By the end of this lesson you'll be able to make a query call itself to walk a hierarchy from top to bottom — an org chart, a category tree, or any graph — tracking how deep each row sits, building a readable path, and stopping safely before the query runs forever.

What You'll Learn

✓Write the WITH RECURSIVE anchor + recursive-member shape
✓Understand how the anchor seeds and the recursive part iterates
✓Walk an employee → manager hierarchy from the top down
✓Track depth/level on every row as you descend
✓Build a path string to order and indent the tree
✓Guard against infinite loops and runaway recursion

Before you start: finish Advanced Aggregations so you're comfortable with GROUP BY, JOIN, and ordinary (non-recursive) CTEs. The in-browser editor lets you write and edit SQL; to run it, copy your query into a free playground like db-fiddle.com (pick PostgreSQL) or sqliteonline.com. Every example below shows the expected result so you can check yourself.

Our Sample Table: `employees`

Every query in this lesson runs against this tiny employees table. Each person points at their boss through manager_id. Ada is the CEO, so her manager_id is NULL — she's the top of the tree.

Result:

id	name	manager_id
1	Ada	NULL
2	Brian	1
3	Carla	1
4	Dan	2
5	Eve	4

Read it as a chain of command: Ada manages Brian and Carla; Brian manages Dan; Dan manages Eve. That's four levels deep, which is exactly what the recursion will rebuild.

1. What a Recursive CTE Is

A CTE (Common Table Expression) is a named, temporary result you define with WITH and then query — like a throwaway view that lasts one statement. A recursive CTE is one that refers to itself, so it can repeat a step to walk data of unknown depth.

It always has exactly three pieces, glued together inside WITH RECURSIVE name AS ( ... ):

Anchor member — a normal SELECT that produces the starting row(s). It runs once.
UNION ALL — joins the anchor to the recursive part and keeps every row.
Recursive member — a SELECT that references the CTE by name. It runs again and again, each time feeding on the rows the previous pass produced, until it produces no new rows.

🪆 Real-world analogy

Think of standing at the top of a staircase you can't see the bottom of. The anchor is the step you're on. The recursive member is the rule "step down one more". You keep applying that rule until there's no step left — that "no step left" is the WHERE condition that stops the recursion.

The three parts

Anchor, UNION ALL, recursive member — counting 1 to 5.

Try it Yourself »

SQL

-- Every recursive CTE has the SAME three parts:
WITH RECURSIVE cte AS (
    SELECT 1 AS n            -- 1) ANCHOR: the seed row(s). Runs ONCE.
    UNION ALL                -- 2) Must be UNION ALL (not UNION) to keep every row.
    SELECT n + 1             -- 3) RECURSIVE member: refers back to "cte".
    FROM cte
    WHERE n < 5              --    The WHERE is the STOP condition.
)
SELECT n FROM cte;

-- How it runs: the anchor produces n=1. The recursive member then runs
-- against ONLY the ro
...

Result — 5 rows:

n
1
2
3
4
5

2. How the Anchor Seeds and the Recursion Iterates

The engine keeps a small "working table" of the rows produced last time. The anchor fills it once; then the recursive member runs against just those rows, appends whatever it finds, and that new batch becomes the next working table. When a pass adds zero rows, recursion stops.

Key idea: the recursive member does not see the whole growing result — it only sees the rows added on the previous step. That's why you must carry forward any value you'll need next (like a running depth or path) as a column.

Watch it iterate

The working table after each step: 1, then 2, 3, 4, 5.

Try it Yourself »

SQL

-- A counter from 1 to 5, watching the iterations.
WITH RECURSIVE counter AS (
    SELECT 1 AS n                 -- anchor: {1}
    UNION ALL
    SELECT n + 1 FROM counter
    WHERE n < 5                   -- recursive part keeps going while n < 5
)
SELECT n FROM counter;

-- Iteration by iteration the "working table" holds:
--   anchor  -> 1
--   step 1  -> 2   (from 1+1)
--   step 2  -> 3
--   step 3  -> 4
--   step 4  -> 5   (5 fails the WHERE next time, so it STOPS)
-- Result: one column n w
...

Result — 5 rows:

n
1
2
3
4
5

3. Traversing a Hierarchy (Org Chart)

This is the headline use case. You can't write a fixed number of JOINs because you don't know how many levels deep the org goes. Recursion solves it: anchor on the CEO (the row whose manager_id IS NULL), then the recursive member joins employees back to the CTE on e.manager_id = oc.id — "give me everyone whose boss is already in the tree". A depth + 1 column counts how many levels down each person sits.

Walk the org chart

Anchor on the CEO, then descend level by level with a depth column.

Try it Yourself »

SQL

-- Walk the company org chart from the CEO downward.
-- employees(id, name, manager_id); the CEO's manager_id IS NULL.
WITH RECURSIVE org_chart AS (
    -- ANCHOR: start at the one person with no manager (the CEO).
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- RECURSIVE: each pass finds the DIRECT reports of rows we already have.
    SELECT e.id, e.name, e.manager_id, oc.depth + 1
    FROM employees e
    JOIN org_chart oc ON e
...

Result — 5 rows — hand-verified:

depth	name
0	Ada
1	Brian
1	Carla
2	Dan
3	Eve

Trace it: the anchor adds Ada at depth 0. Pass 1 finds Ada's reports — Brian and Carla at depth 1. Pass 2 finds Brian's report Dan at depth 2 (Carla has none). Pass 3 finds Dan's report Eve at depth 3. Pass 4 finds nobody under Eve, so it stops.

Your Turn: complete the JOIN

The recursive member is missing the join condition that walks down the hierarchy. Fill in the blank so each employee links to a manager who is already in the tree. The expected result is in the comments.

🎯 Your Turn: the recursive JOIN

Replace ___ with the join that descends the tree.

Try it Yourself »

SQL

-- 🎯 YOUR TURN — complete the recursive member's JOIN to walk DOWN the tree.
-- Goal: list every employee with how deep they sit below the CEO.

WITH RECURSIVE org_chart AS (
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL          -- anchor: the CEO

    UNION ALL

    SELECT e.id, e.name, e.manager_id, oc.depth + 1
    FROM employees e
    JOIN org_chart oc ON ___          -- 👉 link each employee to a row already
                                   
...

4. Tracking Depth and Building a Path

Two columns make recursive results genuinely useful. A depth counter (seed it in the anchor, add 1 in the recursive member) tells you how far down each row is. A path string (seed it with the root name, append || ' > ' || name each step) records the full chain — and because the path sorts in tree order, you can ORDER BY path to print the hierarchy in the right shape.

💡 Pro Tip — order by the path

Recursive results come back in an arbitrary order by default. Carrying a path column and doing ORDER BY path is the simplest way to get parent-then-children tree ordering.

Depth + path column

Seed depth and path in the anchor; extend both in the recursive member.

Try it Yourself »

SQL

-- Add a "path" string so you can ORDER BY it and indent the tree.
WITH RECURSIVE org_chart AS (
    SELECT id, name, manager_id, 0 AS depth,
           name AS path                         -- anchor seeds the path with the CEO
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.manager_id, oc.depth + 1,
           oc.path || ' > ' || e.name           -- append each name as we descend
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELE
...

Result — 5 rows, ordered by path:

depth	name	path
0	Ada	Ada
1	Brian	Ada > Brian
2	Dan	Ada > Brian > Dan
3	Eve	Ada > Brian > Dan > Eve
1	Carla	Ada > Carla

Your Turn: add a level counter

Two blanks: give the CEO a starting number in the anchor, then make each level one deeper in the recursive member.

🎯 Your Turn: depth counter

Seed the level at 0 and add 1 on each recursive step.

Try it Yourself »

SQL

-- 🎯 YOUR TURN — add a depth/level counter to this traversal.
-- The anchor starts the count; the recursive member adds 1 each step.

WITH RECURSIVE org_chart AS (
    SELECT id, name, manager_id, ___ AS level    -- 👉 the starting number for the CEO (0)
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.manager_id, ___        -- 👉 one deeper than the parent: oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT level, 
...

5. Graphs & Guarding Against Infinite Loops

An org chart is a clean tree — every person has exactly one manager, so the recursion naturally ends. A graph is looser: nodes can point at each other in cycles (A → B → C → A). If your data has a cycle, the recursive member never stops adding rows, and the query runs forever.

Two defences, used together: keep a path array of visited nodes and skip any node already in it (WHERE NOT next = ANY(path)), and add a hard depth limit (WHERE depth < 100) as a backstop. Even on trees, a depth cap is cheap insurance against bad data.

Watch out: without cycle prevention, a single loop in your data turns a recursive CTE into an infinite query that your database will eventually kill — or that will exhaust memory first. Always cap depth or track the path on graph data. PostgreSQL 14+ also offers a built-in CYCLE clause.

Common Errors (and the fix)

Using UNION instead of UNION ALL: UNION de-duplicates after every step, which is slow and can hide rows you need. Recursive CTEs should almost always use UNION ALL.
No termination → infinite loop: if the recursive member's WHERE never goes false (or you forget it on cyclic data), the query never ends. Add a stop condition like WHERE depth < 100 or a NOT ... = ANY(path) cycle check.
"syntax error at or near RECURSIVE" / unexpected results: in PostgreSQL the keyword is required — it's WITH RECURSIVE, not just WITH. (SQL Server and Oracle infer it, but Postgres and SQLite need it.)
Column count / type mismatch across UNION ALL: the anchor and recursive member must return the same number of columns in the same order with compatible types. Cast the anchor if needed, e.g. 0 AS depth pairs with oc.depth + 1.
Self-reference used twice: the recursive member may reference the CTE name only once. To combine two recursive branches, restructure rather than joining the CTE to itself.

📘 Quick Reference

Syntax	Purpose
WITH RECURSIVE cte AS (...)	Declare a self-referencing CTE
anchor SELECT	The seed row(s); runs once
UNION ALL	Glue anchor + recursive part, keep all rows
recursive SELECT ... FROM cte	Repeats, feeding on the last batch of rows
WHERE n < 100	Stop condition / depth guard
0 AS depth, depth + 1	Track how deep each row sits
JOIN cte ON e.manager_id = cte.id	Descend a parent → child hierarchy
NOT next = ANY(path)	Skip already-visited nodes (cycle guard)

Frequently Asked Questions

Q: Why UNION ALL and not UNION?

UNION removes duplicates on every iteration, adding overhead and occasionally dropping rows you legitimately want. UNION ALL keeps everything, which is what hierarchy and graph walks need. Use UNION only if you have a specific reason to de-duplicate.

Q: Do I always need the RECURSIVE keyword?

In PostgreSQL and SQLite, yes — leaving it off gives a syntax error or treats the query as non-recursive. SQL Server and Oracle figure it out from the self-reference, so they don't require it. Writing WITH RECURSIVE is the portable, clear choice.

Q: How do I stop it running forever?

The recursive member needs a condition that eventually becomes false. On trees that happens naturally (you run out of children). On graphs with cycles, add a depth cap (WHERE depth < 100) and/or track visited nodes in a path array and exclude them.

Q: Why can't the recursive part see all the rows so far?

By design it only sees the rows produced on the previous step (the "working table"). That keeps each iteration cheap. If you need a value later — depth, path, a running total — carry it forward as a column on every row.

Mini-Challenge: Everyone Under Brian

Put it together — a brief, a starter outline, and the expected result in the comments. Write it, then copy it into a playground to confirm.

🎯 Mini-Challenge

All direct + indirect reports under a chosen manager (id = 2).

Try it Yourself »

SQL

-- 🎯 MINI-CHALLENGE
-- List EVERYONE who reports (directly or indirectly) to Brian (id = 2),
-- using only what this lesson covered.
--   1. ANCHOR: select the row WHERE id = 2  (Brian himself)
--   2. RECURSIVE member: JOIN employees to the CTE on manager_id = id
--   3. Combine the two halves with UNION ALL
--   4. Return their names
--
-- ✅ Expected: Brian, Dan, Eve   (Carla is NOT under Brian, so she is excluded)

-- WITH RECURSIVE subtree AS (
--     ...your anchor here...
--     UNION ALL
...

🎉 Lesson Complete

✅ A recursive CTE is WITH RECURSIVE cte AS (anchor UNION ALL recursive-member)
✅ The anchor seeds once; the recursive member iterates on the last batch until no new rows appear
✅ Walk a hierarchy by joining the table back to the CTE on manager_id = id
✅ Carry a depth and a path column to measure and order the tree
✅ Guard graphs with a depth limit and a visited-node check so they can't loop forever
✅ Next: Temporal Tables — query data as it looked at any point in time

Recursive CTEs: Hierarchies, Graphs & Trees

What You'll Learn

Our Sample Table: employees

1. What a Recursive CTE Is

The three parts

2. How the Anchor Seeds and the Recursion Iterates

Watch it iterate

3. Traversing a Hierarchy (Org Chart)

Walk the org chart

Your Turn: complete the JOIN

🎯 Your Turn: the recursive JOIN

4. Tracking Depth and Building a Path

Depth + path column

Your Turn: add a level counter

🎯 Your Turn: depth counter

5. Graphs & Guarding Against Infinite Loops

Common Errors (and the fix)

📘 Quick Reference

Frequently Asked Questions

Mini-Challenge: Everyone Under Brian

🎯 Mini-Challenge

🎉 Lesson Complete

Cookie & Privacy Settings

Our Sample Table: `employees`