Skip to content
Tutorials Center
My learning

Topics

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.

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

SQL Org chart with level and path ยท database: company

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.

SQL Chain of command upward ยท database: company

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.

SQL Headcount below each manager ยท database: company

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

SQL Numbers and dates

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.

SQL Routes through a small graph, avoiding loops

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
SQL
-- 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 ALL is faster and keeps duplicates; UNION removes 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.