SQL Reference ยท JSON and Special Features
SQL recursive query patterns
Concept guide to recursive SQL patterns: walking org charts, building paths, generating number and date series, and stopping cycles in graphs.
A recursive common table expression (WITH RECURSIVE) has two parts joined by UNION ALL: an anchor that produces the starting rows, and a recursive step that refers to the CTE itself and produces more rows from the ones found so far. The database repeats the step until it returns no new rows. The syntax is explained in WITH RECURSIVE; this page shows the shapes that come up again and again.
Most recursive queries are one of four patterns: walking down a hierarchy, building a path or level number, generating a sequence, or traversing a graph without looping forever.
WITH RECURSIVE walk(id, depth) AS ( SELECT id, 0 FROM employees WHERE manager_id IS NULL -- anchor UNION ALL SELECT e.id, walk.depth + 1 -- recursive step FROM employees AS e JOIN walk ON e.manager_id = walk.id ) SELECT * FROM walk;
Pattern 1: walking a hierarchy
Starting from the top of the company, find everyone below, with their depth and a readable path.
Press Run (or Ctrl + Enter) to see the result here.
Sorting by the path puts every manager directly above their reports. To walk up instead (everyone above one employee), start at that employee and join on e.id = chain.manager_id.
Press Run (or Ctrl + Enter) to see the result here.
Pattern 2: aggregating a subtree
Combine the walk with GROUP BY to answer "how large is each manager's organization?" Keep the root of each subtree as a column while you recurse.
Press Run (or Ctrl + Enter) to see the result here.
The anchor lists every employee with a manager as their own root, so every subtree is counted (the company head, who has no manager, is not listed as a root). COUNT(*) - 1 leaves out the manager, and HAVING removes employees with nobody below them.
Pattern 3: generating series
A recursive CTE can invent rows from nothing, which gives you number lists and calendar tables on databases without generate_series().
Press Run (or Ctrl + Enter) to see the result here.
Always include a stop condition (WHERE i < 5); without it the query runs until the database stops it. PostgreSQL and SQL Server offer generate_series() / numbers tables; SQLite has the generate_series extension only when loaded, so recursion is the portable choice.
Pattern 4: graphs and cycles
A hierarchy has one parent per node; a graph (roads, follows, dependencies) can have loops. Without a guard, a loop makes the recursion infinite. Two defenses: carry a visited path and refuse to revisit a node, or use UNION (not UNION ALL), which discards duplicate rows.
Press Run (or Ctrl + Enter) to see the result here.
INSTR(path, to_city) = 0 skips cities already on the path, so the Madrid and Paris loop is ignored. For large graphs, store the path in a way that cannot give false matches (a delimiter on both ends, or an array in PostgreSQL).
Dialect differences
| Topic | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
| Keyword | WITH RECURSIVE |
WITH RECURSIVE |
WITH RECURSIVE (8.0+) |
WITH (no RECURSIVE) |
WITH (11gR2+) |
| Depth limit | None by default | None | cte_max_recursion_depth = 1000 |
OPTION (MAXRECURSION n), default 100 |
None |
| Cycle detection | Manual path / UNION |
CYCLE clause (14+), arrays |
Manual | Manual | CYCLE clause |
| Ordering | Sort by a path column | SEARCH DEPTH/BREADTH FIRST (14+) |
Path | Path | SEARCH clause |
| Series helper | Recursion | generate_series() |
Recursion | GENERATE_SERIES (2022+) |
CONNECT BY LEVEL |
-- PostgreSQL 14+: built-in cycle detection and ordering WITH RECURSIVE route AS ( SELECT 'Lisbon'::text AS city UNION ALL SELECT e.to_city FROM route JOIN edges e ON e.from_city = route.city ) SEARCH DEPTH FIRST BY city SET ordering CYCLE city SET is_cycle USING path SELECT * FROM route WHERE NOT is_cycle; -- Oracle: older hierarchical syntax SELECT LEVEL, first_name FROM employees START WITH manager_id IS NULL CONNECT BY PRIOR id = manager_id;
Usage notes
- Termination is your job. Add a depth limit or a visited check. The recursion limits above are safety nets, not logic.
- Columns must match. The anchor and the step must return the same number of columns with compatible types; the column types come from the anchor.
UNION ALLis faster and keeps duplicates;UNIONremoves them and can stop some cycles.- Index the join column (
manager_id): the step runs once per level and joins on it. - Do not use recursion for deep paths on huge data when a closure table or a stored path column would let you answer with a simple
LIKE.
Related
- WITH RECURSIVE, WITH (CTE), UNION, self join, CASE
- Lessons: Recursive CTEs, Self join, CTEs