Recursive CTEs

Reviewed & published by Brayan K

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.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

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

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.

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.

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 table this lesson uses. Every later block queries it, and the
-- "Try it Yourself" button carries this setup along so each snippet runs.
-- manager_id points at another row's id; the CEO's is NULL, which is what
-- makes the org chart walkable.
CREATE TABLE employees (
    id         INTEGER PRIMARY KEY,
    name       TEXT,
    title      TEXT,
    manager_id INTEGER,
    salary     INTEGER,
    hire_date  TEXT,
    email      TEXT
);

INSERT INTO employees (id, name, title, manager_id, salary, hire_date, email) VALUES
    (1, 'Ada',   'Chief Executive',  NULL, 180000, '2019-02-11', '[email protected]'),
    (2, 'Brian', 'VP Engineering',      1, 140000, '2020-06-01', '[email protected]'),
    (3, 'Carla', 'VP Sales',            1, 135000, '2021-09-20', '[email protected]'),
    (4, 'Dan',   'Engineer',            2,  75000, '2024-01-15', '[email protected]'),
    (5, 'Eve',   'Junior Engineer',     4,  62000, '2024-07-08', '[email protected]');

-- 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 rows added last time, over and over, until it adds
-- no new rows. Here it stops once n reaches 5.

-- ✅ Expected result:
-- 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.

-- 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)

-- ✅ Expected result:
-- 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 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.manager_id = oc.id   -- "my manager is already in the tree"
)
SELECT depth, name FROM org_chart
ORDER BY depth, name;

-- depth 0 = CEO, depth 1 = their reports, depth 2 = reports-of-reports, ...
-- Each level is one trip around the recursion.

-- ✅ Expected result:
-- 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 — 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
                                      --    in the tree: e.manager_id = oc.id
)
SELECT depth, name FROM org_chart ORDER BY depth, name;

-- ✅ Expected result (5 rows):
--    0 | Ada      (CEO)
--    1 | Brian
--    1 | Carla
--    2 | Dan
--    3 | Eve

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.

-- 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
)
SELECT depth, name, path
FROM org_chart
ORDER BY path;        -- the path string puts each branch in tree order

-- The path doubles as a reporting chain: Ada > Brian > Dan > Eve.

-- ✅ Expected result:
-- 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 — 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, name FROM org_chart ORDER BY level, name;

-- ✅ Expected result (5 rows): Ada=0, Brian=1, Carla=1, Dan=2, Eve=3

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.

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.

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
-- 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
--     ...your recursive member here...
-- )
-- SELECT name FROM subtree;

🎉 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

Practice quiz

Continue this course

• Previous: Advanced Aggregations & Pivot/Unpivot Techniques
• Next: Temporal Tables & Time-Travel Queries — Track historical data with system-versioned temporal tables and time-travel queries
• Quick reference: SQL cheat sheet › Subqueries & CTEs
• From the blog: SQL Joins Explained With Examples