1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

Recursive CTE in SQL: Hierarchies, Paths, Series and Cycles

A recursive CTE is a query that refers to itself, letting SQL walk trees and graphs of any depth. Learn the anchor and recursive parts, build an org chart with levels and paths, find all managers above an employee, count team sizes, generate date series, and stop infinite loops on cyclic data, with every query run.

9 min read
Recursive CTE in SQL: hierarchies, paths, series and cycles

Key takeaways

  • A recursive CTE has an anchor query (the starting rows) joined by UNION ALL to a recursive query that references the CTE itself.
  • It runs level by level until the recursive part returns no new rows.
  • Carry a level counter and a path string to get depth and breadcrumbs in one query.
  • On cyclic data, add a guard such as a visited path, or the query never ends.

Org charts, category trees, folder structures, bills of materials, comment threads and flight routes all have one thing in common: rows point to other rows in the same table, to any depth. A normal join reaches one level; to go deeper you would need one join per level and you rarely know how many there are. A recursive CTE solves that in standard SQL with a single query.

All queries below ran on SQLite 3.50.4 and the output is copied from the run. The same syntax works in PostgreSQL, MySQL 8+ and Oracle; SQL Server omits the RECURSIVE keyword. If CTEs are new to you, read our SQL CTE guide first.

What is a recursive CTE?

A common table expression (CTE) is a named subquery introduced with WITH. A recursive CTE is one whose definition refers to its own name. The database evaluates it iteratively: it runs the anchor once, feeds those rows into the recursive part, feeds the new rows in again, and keeps going until an iteration produces nothing. The final result is the union of every iteration.

Anchor member and recursive member

WITH RECURSIVE cte_name (columns) AS (
    -- anchor member: the starting rows
    SELECT columns FROM some_table WHERE start_condition
    UNION ALL
    -- recursive member: refers to cte_name
    SELECT columns FROM some_table JOIN cte_name ON join_condition
)
SELECT * FROM cte_name;

The anchor must not reference the CTE. The recursive member must reference it exactly once, typically in a join, and must eventually stop producing rows. The column list and types of both parts must match.

The simplest example: counting to five

WITH RECURSIVE n(x) AS (
    SELECT 1
    UNION ALL
    SELECT x + 1 FROM n WHERE x < 5
)
SELECT x FROM n;
-- 1, 2, 3, 4, 5

The anchor produces 1. Each iteration adds 1 to the previous row, and the WHERE x < 5 condition makes the recursive part return nothing once 5 is reached, which ends the recursion. That stopping condition is the most important line in any recursive CTE.

The sample org chart

CREATE TABLE emp (id INT PRIMARY KEY, name TEXT, manager_id INT);
INSERT INTO emp VALUES
 (1,'Asha',NULL), (2,'Ravi',1), (3,'Meera',1), (4,'Kabir',2),
 (5,'Lina',2),    (6,'Zoya',4), (7,'Dev',3);

Asha leads the company, Ravi and Meera report to Asha, Kabir and Lina to Ravi, Dev to Meera, and Zoya to Kabir: four levels in an adjacency list, where each row stores its parent. Our self join guide shows how far a single join gets you.

Walking down a tree: levels and paths

WITH RECURSIVE chain(id, name, lvl, path) AS (
    SELECT id, name, 0, name FROM emp WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, c.lvl + 1, c.path || ' > ' || e.name
    FROM emp e JOIN chain c ON e.manager_id = c.id
)
SELECT lvl, name, path FROM chain ORDER BY path;

-- 0 | Asha  | Asha
-- 1 | Meera | Asha > Meera
-- 2 | Dev   | Asha > Meera > Dev
-- 1 | Ravi  | Asha > Ravi
-- 2 | Kabir | Asha > Ravi > Kabir
-- 3 | Zoya  | Asha > Ravi > Kabir > Zoya
-- 2 | Lina  | Asha > Ravi > Lina

The anchor picks the root (no manager). Each iteration joins employees whose manager is in the previous level, adds one to the level and appends the name to the path. Sorting by the path string produces a natural depth-first outline, ready to indent in a UI. The || operator concatenates text in SQLite, PostgreSQL and Oracle; use CONCAT in MySQL and + or CONCAT in SQL Server.

Walking up a tree: all managers above someone

WITH RECURSIVE up(id, name, manager_id) AS (
    SELECT id, name, manager_id FROM emp WHERE name = 'Zoya'
    UNION ALL
    SELECT e.id, e.name, e.manager_id FROM emp e JOIN up ON e.id = up.manager_id
)
SELECT name FROM up;
-- Zoya, Kabir, Ravi, Asha

Reverse the join direction and the CTE climbs from a leaf to the root. This is how you build breadcrumbs for a category page, check approval chains or find which department a team belongs to.

Counting everyone below each manager

WITH RECURSIVE sub(root, id) AS (
    SELECT id, id FROM emp
    UNION ALL
    SELECT s.root, e.id FROM emp e JOIN sub s ON e.manager_id = s.id
)
SELECT e.name, COUNT(*) - 1 AS reports
FROM sub s JOIN emp e ON e.id = s.root
GROUP BY e.name HAVING COUNT(*) > 1 ORDER BY reports DESC;

-- Asha | 6
-- Ravi | 3
-- Meera | 1
-- Kabir | 1

Here the anchor starts a walk from every employee, and each walk carries its starting point as root. Counting rows per root, minus the root itself, gives the total number of direct and indirect reports. Ravi has three: Kabir, Lina and Zoya. The GROUP BY and HAVING step turns paths into totals.

Generating a date series

WITH RECURSIVE d(day) AS (
    SELECT date('2026-11-27')
    UNION ALL
    SELECT date(day, '+1 day') FROM d WHERE day < '2026-12-01'
)
SELECT day FROM d;
-- 2026-11-27, 2026-11-28, 2026-11-29, 2026-11-30, 2026-12-01

Recursive CTEs are also the portable way to generate rows from nothing: a calendar for a report, so that days with zero sales still appear after a LEFT JOIN, or a list of numbers for testing. PostgreSQL has generate_series for this; elsewhere a recursive CTE does the job. Date arithmetic syntax differs by database, as our SQL date functions guide shows.

Tip: left join your facts to a generated calendar to fill gaps in time-series charts instead of handling missing days in application code.

Handling cycles in graph data

Trees have no loops, but general graphs often do. We created edges A to B, B to C, C to A and C to D:

-- UNION removes duplicate rows, so the loop dies out
WITH RECURSIVE r(node) AS (
    SELECT 'A'
    UNION
    SELECT e.b FROM edges e JOIN r ON e.a = r.node
)
SELECT node FROM r;
-- A, B, C, D

-- UNION ALL plus a visited path as the guard
WITH RECURSIVE r(node, depth, path) AS (
    SELECT 'A', 0, ',A,'
    UNION ALL
    SELECT e.b, depth + 1, path || e.b || ','
    FROM edges e JOIN r ON e.a = r.node
    WHERE instr(path, ',' || e.b || ',') = 0
)
SELECT node, depth, path FROM r;
-- A 0 ,A, | B 1 ,A,B, | C 2 ,A,B,C, | D 3 ,A,B,C,D,

With UNION ALL and no guard, A to B to C to A would repeat forever. Two fixes: plain UNION discards rows already produced, which works when the row is just the node, as in the first query. When you carry extra columns such as depth, every row is new, so you need an explicit visited check: the second query keeps a comma-wrapped path and refuses to step onto a node already in it. PostgreSQL 14+ also offers a built-in CYCLE clause (not run here).

Limits and safety nets

WITH RECURSIVE r(x) AS (SELECT 1 UNION ALL SELECT x + 1 FROM r)
SELECT x FROM r LIMIT 1000000;
-- returned 1,000,000 rows in 0.56 s, then stopped

A recursive CTE without a stopping condition runs until something stops it. In SQLite, an outer LIMIT stops the recursion early, which is a handy safety net during development. SQL Server stops at 100 levels by default (OPTION (MAXRECURSION n) changes it), and MySQL has a cte_max_recursion_depth setting of 1000 by default; those two notes come from vendor documentation. In every database, also add a depth limit such as WHERE lvl < 20 in the recursive member to protect against bad data.

Recursive CTEs across databases

Database Syntax Notes
SQLite 3.8.3+ WITH RECURSIVE Outer LIMIT stops recursion
PostgreSQL WITH RECURSIVE SEARCH and CYCLE clauses in 14+
MySQL 8.0+, MariaDB 10.2+ WITH RECURSIVE Depth limit setting
SQL Server WITH (no RECURSIVE) MAXRECURSION, default 100
Oracle 11gR2+ WITH Also has CONNECT BY

Performance tips

  • Index the column used in the recursive join, such as manager_id, or every level scans the table; see our SQL indexes guide.
  • Filter as early as possible, ideally in the anchor, so fewer rows recurse.
  • Carry only the columns you need; long path strings grow with depth.
  • For very large, read-heavy trees, consider storing a materialised path or a closure table, trading write complexity for fast reads.
  • Prefer UNION ALL with explicit guards over UNION on big data, since UNION must compare every row (see UNION vs UNION ALL).

Final thoughts

A recursive CTE gives SQL the ability to follow relationships to any depth: an anchor to start, a recursive member to take one step, and a condition that eventually stops. With levels and paths you can render trees, climb to the root, total whole subtrees and generate series, all in one query. Index the join column, guard against cycles and runaway depth, and hierarchical data becomes straightforward to query.

Frequently asked questions

What is a recursive CTE in SQL?

It is a common table expression that refers to itself. An anchor query produces the starting rows, and a recursive query joined with UNION ALL repeatedly adds new rows until it returns none.

How do I stop a recursive CTE from running forever?

Add a condition in the recursive member that eventually returns no rows, such as a level limit, and guard cyclic data with a visited path. Database limits like MAXRECURSION act as a final safety net.

Should I use UNION or UNION ALL in a recursive CTE?

UNION ALL is the usual choice and is faster. UNION removes duplicate rows, which can stop simple cycles, but it fails as a guard once you carry columns like depth that make every row unique.

Does SQL Server support recursive CTEs?

Yes, but it uses WITH without the RECURSIVE keyword and stops at 100 levels by default. Change the limit with the MAXRECURSION query option.

How do I get the level or depth of each row?

Start a counter at 0 in the anchor member and add 1 in the recursive member. The same technique builds a path column by concatenating names at each step.

Keep learning

Work with Stack Quora

Need help with testing, WordPress or SEO?

We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

Confusion Matrix Explained: Precision, Recall, F1 and Thresholds in Python

Confusion matrix explained with Python: TP, FP, FN, TN, precision, recall, F1, the accuracy trap, threshold choice by cost and…

Read article →
Software Development

API Pagination: Offset vs Cursor vs Keyset, Tested on 1 Million Rows

API pagination explained: offset vs keyset and cursor pagination timed on 1 million rows, duplicates when data changes, the ties…

Read article →
DevOps

Git Submodule Explained: Add, Clone, Update and Remove Safely

Git submodule explained: add, clone, update –init, update –remote, the detached HEAD trap, foreach and clean removal, tested step by…

Read article →
Test Automation

Pytest tmp path Explained: tmp_path, tmp_path_factory and Temp Files

Pytest tmp_path explained: per-test temporary folders, tmp_path_factory for shared files, monkeypatch.chdir, legacy tmpdir, retention and basetemp.

Read article →
Software Testing

Test Case Prioritization: 6 Techniques Compared With APFD

Test case prioritization compared: risk, coverage, cost, history and random orders scored with APFD and time to find all faults…

Read article →
Databases & SQL

Full Text Search in SQL: FTS5, Ranking, Phrases and Speed

Full text search in SQL with SQLite FTS5: MATCH queries, phrases, prefixes, NEAR, stemming, BM25 ranking, snippets, two gotchas and…

Read article →
Databases & SQL

Generated Columns in SQL: Virtual vs Stored, Indexes and Limits

Generated columns in SQL explained: virtual vs stored, automatic updates, write errors, storage cost, indexing, JSON fields and support in…

Read article →
Software Development

Object Freeze in JavaScript: Shallow vs Deep, Seal and const

Object freeze in JavaScript explained: freeze vs seal vs const, strict mode errors, shallow vs deep freeze, frozen arrays, Map…

Read article →
Software Development

Python hashlib: SHA-256, File Hashes, HMAC and Password Hashing

Python hashlib explained: SHA-256 for strings and files, algorithm speeds, why passwords need PBKDF2 or scrypt, safe verification and HMAC…

Read article →
Software Development

Python UUID Explained: uuid1, uuid4, uuid5, uuid7 and Database Keys

Python UUID explained: uuid4, uuid5, uuid1 privacy, time-ordered uuid7, parsing, storage formats and UUIDs as database keys, tested on Python…

Read article →
Artificial Intelligence

Random Forest in Python: Bagging, Feature Sampling and OOB From Scratch

Random forest in Python from scratch: bootstrap sampling, feature sampling, number of trees, out-of-bag evaluation and impurity vs permutation importance.

Read article →
Software Development

MIME Types Explained: Content-Type, nosniff and a Common Types List

MIME types explained and tested in a browser: Content-Type, nosniff, scripts and stylesheets with wrong types, a common MIME types…

Read article →
DevOps

Git Clean Explained: Remove Untracked Files Without Regret

Git clean explained with dry runs: -n, -f, -d, -x, -X, exclusions, paths, nested repositories and interactive mode, plus a…

Read article →
Test Automation

Pytest approx: Comparing Floats With rel, abs and Collections

Pytest approx explained: default tolerances, the near-zero trap, rel and abs, lists and dicts, NaN, NumPy, and how it differs…

Read article →
Software Testing

Accessibility Testing: WCAG Checklist, Automated Tools and Manual Checks

Accessibility testing explained: WCAG basics, an axe-core scan of a broken and a fixed page, what automation missed, contrast checks…

Read article →
Databases & SQL

SQL UNPIVOT: Turn Columns Into Rows, 4 Ways Tested

SQL UNPIVOT explained: turn columns into rows with UNION ALL, CROSS JOIN with CASE, JSON functions or native UNPIVOT, with…

Read article →
Software Development

Intl NumberFormat in JavaScript: Currency, Compact, Percent and Units

Intl.NumberFormat in JavaScript: locales, currency, compact notation, percent, units, rounding, formatToParts, the non-breaking space trap and formatter reuse.

Read article →
Databases & SQL

SQL EXPLAIN Plan: How to Read Query Plans and Fix Slow Queries

SQL EXPLAIN plan explained: read scans, index searches, covering indexes and temporary sorts, with before and after timings for five…

Read article →
Artificial Intelligence

Decision Tree in Python: Gini, Entropy and Overfitting From Scratch

Decision tree in Python from scratch: Gini impurity, entropy, information gain, best splits, readable rules, and how depth and leaf…

Read article →
DevOps

Git Blame Explained: Find Who Changed a Line, and Why

Git blame explained with a real repository: ignore whitespace, line ranges and functions, ignore-revs files for formatting commits, moved code…

Read article →
Test Automation

Pytest Benchmark: Measure Speed and Catch Performance Regressions

Pytest benchmark tutorial: the benchmark fixture, reading results, parametrized and pedantic benchmarks, saving baselines and failing the build on regressions.

Read article →
Software Testing

Defect Density: Formula, Worked Examples and How to Use It

Defect density explained: the formula, a five-module worked example, per KLOC vs per function point, severity weighting, defect removal efficiency…

Read article →
Databases & SQL

Case Insensitive Search in SQL: LOWER, LIKE, COLLATE and Indexes

Case insensitive search in SQL: LOWER, LIKE, COLLATE NOCASE and expression indexes compared with query plans, plus accented letters and…

Read article →
Databases & SQL

ON DELETE CASCADE in SQL: CASCADE vs SET NULL vs RESTRICT

ON DELETE CASCADE in SQL tested: CASCADE vs SET NULL vs RESTRICT, multi-level cascades, the SQLite foreign key pragma, row…

Read article →
Artificial Intelligence

Logistic Regression in Python: Sigmoid, Log Loss and Gradient Descent From Scratch

Logistic regression in Python from scratch: sigmoid, log loss, gradient descent, odds ratios, thresholds, sigmoid overflow and separable data, tested…

Read article →
DevOps

Git Worktree Explained: Work on Two Branches at Once

Git worktree explained: add, list, remove, prune, lock and detached worktrees, a hotfix workflow, and the errors you will hit,…

Read article →
Test Automation

Pytest ini Explained: addopts, markers, testpaths and Precedence

Pytest ini explained: testpaths, addopts, strict markers, xfail_strict, filterwarnings, minversion, config file precedence and command-line overrides, tested.

Read article →
Software Testing

Test Estimation Techniques: WBS, Three Point PERT and Monte Carlo

Test estimation techniques worked through: WBS, three point PERT, total uncertainty, a Monte Carlo check, percentage rules and Wideband Delphi.

Read article →
Artificial Intelligence

Linear Regression in Python: Least Squares From Scratch, With R Squared

Linear regression in Python from scratch: closed form, NumPy least squares and gradient descent, R squared, RMSE, outliers and curved…

Read article →
DevOps

Git Remote Explained: Add, Set-URL, Rename, Remove and Push URLs

Git remote explained: add, set-url, separate push URLs, rename, remove and prune, plus how to avoid leaking tokens in remote…

Read article →
Test Automation

Pytest Mock: mocker.patch, spy, side_effect and autospec Explained

Pytest mock explained: mocker.patch, return_value, side_effect, spy, autospec and PropertyMock, with tested examples for APIs, clocks and retries.

Read article →
Software Testing

System Testing Explained: Types, Process and a Real End-to-End Example

System testing explained: types, entry and exit criteria, the process, and a real end-to-end test suite run against a small…

Read article →
Artificial Intelligence

Naive Bayes Classifier Explained: Build a Spam Filter in Python

Naive Bayes classifier explained by building a spam filter in Python: Bayes rule, the naive assumption, Laplace smoothing, log probabilities…

Read article →
DevOps

Git Branch Explained: Create, Switch, Rename, Delete and Recover

Git branch explained with tested commands: list, create, switch, rename, delete, merged checks, recovering deleted branches, remote tracking and naming.

Read article →
Test Automation

Pytest raises: Testing Exceptions, match and excinfo

Pytest raises explained with tested examples: match regexes, excinfo, custom and chained exceptions, parametrized error cases, pytest.warns and common mistakes.

Read article →
Software Testing

Non Functional Testing: Types, Metrics and a Real Load Test

Non functional testing explained: 10 key types, measurable requirements, latency percentiles and what a small real load test revealed about…

Read article →