Recursive Ctes

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.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

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.

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.

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 ( ... ) :

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

This is the headline use case. You can't write a fixed number of JOIN s 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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

Practice quiz

What are the three parts of a recursive CTE?

  • SELECT, FROM, WHERE
  • BEGIN, body, COMMIT
  • Anchor member, UNION ALL, recursive member
  • Index, key, value

Answer: Anchor member, UNION ALL, recursive member. A recursive CTE is always an anchor member, glued with UNION ALL to a recursive member that references the CTE.

How many times does the anchor member of a recursive CTE run?

  • Once
  • Once per row in the table
  • Until the WHERE fails
  • Never

Answer: Once. The anchor produces the seed row(s) and runs exactly once; the recursive member is what repeats.

Why do recursive CTEs almost always use UNION ALL instead of UNION?

  • UNION is invalid in CTEs
  • There is no difference
  • UNION ALL sorts the output
  • UNION ALL keeps every row; UNION de-duplicates each step, which is slow and can drop needed rows

Answer: UNION ALL keeps every row; UNION de-duplicates each step, which is slow and can drop needed rows. UNION removes duplicates on every iteration, adding overhead and sometimes dropping rows; UNION ALL keeps everything.

In each pass, what rows does the recursive member operate on?

  • The whole growing result so far
  • Only the rows added on the previous step (the working table)
  • The original anchor rows only
  • A random sample of rows

Answer: Only the rows added on the previous step (the working table). The recursive member sees only the previous step's rows, which is why you must carry forward values like depth or path as columns.

To walk an org chart downward, what join links each employee to a row already in the tree?

  • e.manager_id = oc.id
  • e.id = oc.id
  • e.name = oc.name
  • e.manager_id = oc.manager_id

Answer: e.manager_id = oc.id. Joining employees to the CTE on e.manager_id = oc.id means 'my manager is already in the tree', descending the hierarchy.

How do you track how deep each row sits in the hierarchy?

  • Use COUNT(*)
  • It is automatic, no column needed
  • Seed a depth column in the anchor and add 1 in the recursive member
  • Use ORDER BY depth

Answer: Seed a depth column in the anchor and add 1 in the recursive member. Seed depth (e.g. 0) in the anchor and compute oc.depth + 1 in the recursive member to count levels.

What stops a recursive CTE that walks a tree from running forever?

  • A fixed number of JOINs
  • It runs out of children, so a pass eventually adds zero new rows
  • The COMMIT statement
  • It never stops on its own

Answer: It runs out of children, so a pass eventually adds zero new rows. On a tree the recursion ends naturally when a pass produces no new rows; on graphs with cycles you need extra guards.

On graph data with cycles, what defends against an infinite loop?

  • Nothing is needed
  • Using UNION instead of UNION ALL
  • Removing the anchor member
  • A path array of visited nodes and/or a hard depth cap

Answer: A path array of visited nodes and/or a hard depth cap. Track visited nodes (WHERE NOT next = ANY(path)) and add a depth limit as a backstop; PostgreSQL 14+ also has a CYCLE clause.

In PostgreSQL, what keyword must precede a self-referencing CTE?

  • WITH only
  • WITH RECURSIVE
  • RECURSIVE WITH
  • LOOP

Answer: WITH RECURSIVE. PostgreSQL and SQLite require WITH RECURSIVE; SQL Server and Oracle infer it from the self-reference.

Across the UNION ALL, what must the anchor and recursive members agree on?

  • Nothing
  • Only the table name
  • The same number of columns in the same order with compatible types
  • The same WHERE clause

Answer: The same number of columns in the same order with compatible types. Both members must return the same column count, order, and compatible types, e.g. 0 AS depth pairs with oc.depth + 1.