Skip to content
Tutorials Center
My learning

Topics

Lesson 48 of 125 ยท Subqueries and Set Operations

Recursive CTEs

Learn recursive CTEs in SQL with WITH RECURSIVE. Generate number and date series, walk an org chart, find manager chains and explore graphs safely.

A recursive CTE is a common table expression that refers to itself. It starts from a few rows, then repeatedly uses the rows it just produced to find more, until no new rows appear. That simple loop lets SQL do things that otherwise need application code: walking an org chart of any depth, listing every day in a month, or finding every city you can reach in a flight network.

Recursive CTEs have a reputation for being hard. They aren't, once you see the two parts and how the database runs them step by step. This lesson builds that picture with tiny examples first and then applies it to the company database, whose employees.manager_id column points at each person's boss.

The two parts of a recursive CTE

Every recursive CTE has the same skeleton:

SQL
WITH RECURSIVE name (columns) AS (
  -- 1. anchor member: the starting rows
  SELECT ...
  UNION ALL
  -- 2. recursive member: new rows built from the previous step
  SELECT ... FROM name WHERE <stop condition>
)
SELECT * FROM name;
  • The anchor member runs once and produces the starting rows.
  • The recursive member references the CTE itself. It runs again and again, each time seeing only the rows produced by the previous step.
  • UNION ALL glues all steps together into the final result.
  • When a step produces no rows, the recursion stops. Your WHERE condition is what eventually makes that happen.

The smallest useful example counts from 1 to 10:

SQL Counting from 1 to 10

Press Run (or Ctrl + Enter) to see the result here.

Here is what happens inside:

Step Input rows (previous step) Rows produced
Anchor none 1
1 1 2
2 2 3
... ... ...
9 9 10
10 10 none (10 < 10 is false), so stop

The database keeps a "working table" holding the previous step's rows. The recursive member reads it, its output becomes the next working table, and every output row is also appended to the final result.

Always have a way out. If the recursive member never returns an empty result (for example, you forget WHERE n < 10), the query runs forever, or until the database's safety limit stops it. While experimenting, adding LIMIT 100 to the final SELECT is a cheap seatbelt; in SQLite that limit also stops the recursion early.

Generating series of numbers and dates

Series are the everyday use of recursion. Need multiplication tables, test data, or one row per hour? Generate them.

A date series is especially useful for reports. If you count orders per day with GROUP BY, days without orders simply vanish. Generate every day first, then LEFT JOIN the real data, and the empty days show up with zero:

SQL Every day, including empty ones ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The result has all ten days from March 1 to March 10, 2025. March 2, 3, 4, 9 and 10 show 0 orders; without the generated series they would be missing from the report entirely. COUNT(o.id) (not COUNT(*)) is what turns the unmatched days into zero, because it ignores the NULLs that the left join produces.

You can carry more than one column through the recursion. This generates the first Fibonacci numbers by keeping the current and next value in each row:

SQL Fibonacci with two columns

Press Run (or Ctrl + Enter) to see the result here.

The fifteenth row shows 377. Each step only needs the row before it, which is exactly what a recursive CTE provides.

Walking a hierarchy: the org chart

Hierarchies stored as "each row points to its parent" are called adjacency lists. Categories with parent categories, folders, comment threads and org charts all look like this. In employees, the CEO has manager_id IS NULL, and everyone else points to a manager.

A self join can find one level of management. A recursive CTE finds all levels, however many there are:

SQL The whole org chart ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

The anchor finds Kofi Okafor, the CEO, at level 0. Step one joins employees to him and finds the seven people who report to him (six department heads and a contractor) at level 1. Step two finds their reports at level 2. Step three finds nobody, so the recursion stops. There's no WHERE stop condition because the data itself runs out.

Two techniques in that query are worth copying:

  • A level column counts the depth. Use it to indent names, filter ("everyone two levels below the CEO") or limit depth (WHERE org.level < 3 in the recursive member).
  • A path column concatenates the names on the way down. Sorting by it puts each manager right above their team, like a folder tree.

You can also start the anchor in the middle. To list everyone in Yara Demir's part of the company, anchor on WHERE id = 2 instead of manager_id IS NULL.

Walking up: the chain of command

Recursion works in the other direction too. Start from one employee and repeatedly join to their manager:

SQL Chain of managers for one employee ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

Viktor Petrov (id 14) is a QA engineer who reports to Farah Tanaka, the head of design, who reports to the CEO. The recursion stops when it reaches the CEO, because the CEO's manager_id is NULL and the join finds no match.

Combining both directions answers real questions, such as "how many people work under each department head, directly or indirectly?" Anchor on all heads, carry the head's id down the tree, then group by it:

SQL Team size under each head ยท database: company

Press Run (or Ctrl + Enter) to see the result here.

COUNT(*) - 1 removes the head from their own count, because the anchor row pairs each head with themselves. Notice that Yara Demir has five people below her even though Engineering has ten employees: some engineers report to the heads of design and sales. The reporting tree and the department column are different facts, and only recursion over manager_id follows the tree.

Graphs and cycles

Org charts are trees: everyone has at most one manager and there are no loops. General graphs, like roads or flight routes, can contain cycles: A connects to B, B connects back to A. A naive recursive CTE would bounce between them forever.

The standard fix is to carry the visited path and refuse to revisit a node. This example builds a small route table and finds every city reachable from Lisbon:

SQL Reachable cities without loops

Press Run (or Ctrl + Enter) to see the result here.

The path column holds visited cities wrapped in commas (,Lisbon,Madrid,). instr() returns 0 when the next city isn't in the path yet, so the route back to Lisbon and the Berlin/Warsaw loop are never followed twice. Wrapping names in commas prevents false matches, such as a city called Paris matching inside Parisville.

Another simple guard is a depth limit, such as WHERE t.stops < 5, which bounds the work even when you can't track paths.

UNION vs UNION ALL: with plain UNION, the database discards rows it has already produced, which also stops some cycles. It only helps when an entire row repeats, though; a row that carries a growing path or counter is never an exact duplicate. Prefer UNION ALL plus an explicit guard.

Splitting a delimited string

Recursion can also chew through a string one piece at a time. Each step cuts off the first item and passes the rest along:

SQL Splitting comma-separated tags

Press Run (or Ctrl + Enter) to see the result here.

The anchor adds a trailing comma so every tag ends with one. The result is four rows: sql, windows, ctes and joins. (In real schemas, prefer a proper table of tags over comma-separated lists; SQLite's json_each is another way to split JSON arrays.)

Rules and limits

The recursive member has restrictions in every database, because it must work one step at a time:

  • It may reference the CTE only once, and not inside a subquery in most databases.
  • It can't use aggregate functions like SUM() or GROUP BY over the recursive reference. Aggregate in the final query instead.
  • Column types are fixed by the anchor. In PostgreSQL and SQL Server, a text column whose anchor value is short can cause type errors when the path grows; cast it in the anchor (for example CAST(name AS VARCHAR(1000)) or ::text). SQLite is relaxed about this.

SQLite adds a useful extra: ORDER BY and LIMIT are allowed inside the recursive CTE. ORDER BY there controls which pending row is expanded next, so ordering by level gives breadth-first order, and ordering by level DESC gives depth-first order.

Dialect differences

Database Keyword Safety limit Extras
SQLite WITH RECURSIVE (keyword optional) none by default ORDER BY/LIMIT inside the CTE
PostgreSQL WITH RECURSIVE required none SEARCH and CYCLE clauses (version 14+)
MySQL 8.0+ WITH RECURSIVE required cte_max_recursion_depth = 1000
SQL Server plain WITH (no RECURSIVE) 100 levels, change with OPTION (MAXRECURSION n)
Oracle plain WITH none SEARCH/CYCLE; older CONNECT BY syntax

Read-only examples of the dialect-specific parts:

SQL
-- SQL Server: no RECURSIVE keyword, and raise the 100-level default
WITH counter (n) AS (
  SELECT 1
  UNION ALL
  SELECT n + 1 FROM counter WHERE n < 500
)
SELECT n FROM counter
OPTION (MAXRECURSION 1000);

-- PostgreSQL: generate_series() makes many series unnecessary
SELECT day::date
FROM generate_series('2025-03-01'::date, '2025-03-10'::date, interval '1 day') AS day;

-- PostgreSQL 14+: built-in cycle detection
WITH RECURSIVE trips (city) AS (
  SELECT 'Lisbon'
  UNION ALL
  SELECT r.to_city FROM trips t JOIN routes r ON r.from_city = t.city
) CYCLE city SET is_cycle USING visited
SELECT * FROM trips WHERE NOT is_cycle;

SQLite has a generate_series table-valued function too, but only as an extension that isn't always compiled in, so the recursive CTE is the portable choice.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What does the recursive member of a recursive CTE see each time it runs?

    Each iteration works on the previous step's output (the working table). All steps are combined into the final result by UNION ALL.
  2. 2. How many rows does this return? WITH RECURSIVE c(n) AS (SELECT 5 UNION ALL SELECT n - 1 FROM c WHERE n > 1) SELECT n FROM c;

    It produces 5, 4, 3, 2, 1. When n is 1, n > 1 is false, the recursive step returns nothing, and the recursion ends.
  3. 3. When does the org-chart query stop, even though it has no WHERE condition?

    A step that produces zero rows ends the recursion. Once the lowest level has no direct reports, the join returns nothing.
  4. 4. Why does the routes example store the visited cities in a path column?

    Graphs can contain cycles. Checking the path with instr() before adding a city stops the query from going around a loop forever.
  5. 5. Which database does NOT accept the RECURSIVE keyword?

    SQL Server (and Oracle) write recursive CTEs with plain WITH. PostgreSQL and MySQL require WITH RECURSIVE, and SQLite accepts it but doesn't require it.

Summary

  • A recursive CTE has an anchor member, UNION ALL, and a recursive member that references the CTE itself.
  • Each step sees only the previous step's rows; the recursion stops when a step returns nothing.
  • Generate number and date series, then LEFT JOIN real data to show gaps as zeros.
  • Walk hierarchies down (reports) or up (managers), carrying level and path columns.
  • In graphs, prevent infinite loops with a visited path or a depth limit.
  • SQL Server and Oracle omit RECURSIVE; SQL Server and MySQL cap the recursion depth by default.

Next: Window Functions, where you'll compute totals, averages and ranks across rows without collapsing them.