A recursive CTE computes a result from a starting set and then repeatedly applies a step to the rows produced so far. It is the standard way to walk org charts, category trees and bill-of-materials in PostgreSQL.
Before you start
You should be comfortable with joins and basic CTEs. This article assumes PostgreSQL syntax; the pattern transfers to other engines with small changes.
Step-by-step walkthrough
Step 1: Write the anchor term
The anchor is the non-recursive query that seeds the working set, for example selecting the root node by id. It runs once and produces the first rows.
Step 2: Add the recursive term
The recursive term joins the table to the CTE’s previous output, such as joining children where parent_id = cte.id. Each iteration consumes the rows from the last round and produces the next level, stopping when the term returns no rows.
Step 3: Guarantee termination
A cycle in the data makes the recursive term produce rows forever, so keep a depth column and stop at a limit, or track visited ids. Use UNION rather than UNION ALL only when duplicate rows should be removed, and note that deduplication can also mask cycles.
Worked scenario
The walk returns every descendant of the root with its depth.
WITH RECURSIVE tree AS (
SELECT id, parent_id, name, 0 AS depth
FROM categories
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.parent_id, c.name, t.depth + 1
FROM categories c
JOIN tree t ON c.parent_id = t.id
)
SELECT id, name, depth
FROM tree
ORDER BY depth, name;Walk through the example
The anchor selects roots with no parent and gives them depth 0. Each iteration joins categories to the growing tree on parent_id = t.id, appending children one level deeper. The query ends when a round finds no matching children, and the final select orders by depth so the hierarchy reads top down.
Common mistake
Omitting a cycle guard on data that can loop (a node whose ancestor points back), which spins until the engine errors or hangs. Another is using UNION ALL on a graph with shared descendants and then being surprised by duplicate rows; deduplicate deliberately with UNION or DISTINCT when the data is a DAG.
Verify the behavior
Assert the root row has depth 0 and each child is exactly one deeper than its parent. Insert a cycle, run with a depth < 10 guard, and confirm the query stops. Compare the recursive result with a hand-computed tree on a small fixture to confirm no level is skipped.
Interview exercise
Why does a recursive CTE on a cyclic graph hang, and how do you bound it?
Answer and reasoning
The recursive term keeps finding the same nodes because a cycle always offers a next row, so the loop never reaches an empty intermediate result. Bound it by carrying a depth and adding WHERE depth < n, or by tracking visited ids and excluding them. In PostgreSQL 14+ you can also use the CYCLE clause to detect and stop on repeats.
Continue learning
Compare set-based alternatives in PostgreSQL CTE purpose and SQL join cardinality. Read the PostgreSQL WITH queries documentation and try the SQL interview questions.