Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Statements and Clauses

SQL WITH RECURSIVE

Reference for SQL WITH RECURSIVE: write recursive common table expressions to walk trees, org charts and number sequences, with termination rules and dialects.

A recursive common table expression (CTE) is a query that refers to its own name. It lets plain SQL walk hierarchies of unknown depth, such as an employee-and-manager chain, a category tree or a folder structure, and generate sequences of numbers or dates without a helper table.

A recursive CTE has two parts joined by UNION or UNION ALL: an anchor member that produces the starting rows, and a recursive member that reads the rows produced in the previous round and produces the next ones. The database repeats the recursive member until it returns no new rows.

SQL
WITH RECURSIVE countdown(n) AS (
  SELECT 3
  UNION ALL
  SELECT n - 1 FROM countdown WHERE n > 1
)
SELECT n FROM countdown;

Try it

SQL Generating a number sequence ยท database: shop

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

SQL Walking the org chart from the CEO down ยท database: company

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

Syntax

SQL
WITH RECURSIVE cte_name (column1, column2, ...) AS (
  anchor_select
  UNION ALL            -- or UNION
  recursive_select     -- refers to cte_name
)
SELECT ... FROM cte_name;
Part Description
anchor_select A query that does not mention cte_name. It runs once and supplies the first batch of rows.
recursive_select A query that reads cte_name (usually in FROM, joined to a base table). It sees only the rows added in the previous round and adds new ones.
UNION ALL Appends every new row. Fastest, but a cycle in the data never ends.
UNION Discards rows that already exist in the result, which also stops many cycles.
column list Optional in some dialects; names the CTE columns. The types come from the anchor member.

Returns: all rows from the anchor plus every round of the recursive member, in no guaranteed order unless you add ORDER BY in the outer query.

Dialect differences

Dialect Notes
SQLite WITH RECURSIVE (the RECURSIVE keyword is accepted and conventional). UNION and UNION ALL both work. The recursive part may use ORDER BY and LIMIT to make a queue.
PostgreSQL WITH RECURSIVE is required. Offers SEARCH and CYCLE clauses (PostgreSQL 14+) for ordering and loop detection.
MySQL WITH RECURSIVE from 8.0. Recursion depth is capped by cte_max_recursion_depth (1000 by default).
SQL Server Write WITH without the word RECURSIVE, and UNION ALL is required. The default limit is 100 levels; raise it with OPTION (MAXRECURSION n).
Oracle WITH without RECURSIVE (recursive subquery factoring, 11gR2+), with an explicit column list. Oracle also has the older CONNECT BY.
SQL
-- SQL Server: no RECURSIVE keyword, and a depth option
WITH countdown(n) AS (
  SELECT 3
  UNION ALL
  SELECT n - 1 FROM countdown WHERE n > 1
)
SELECT n FROM countdown
OPTION (MAXRECURSION 200);

Usage notes

  • Always include a stopping rule. Either the recursive member eventually returns no rows (a WHERE n < 5 guard, or reaching the leaves of a tree), or you bound the output with LIMIT.
  • Cycles loop forever. If the data can contain loops (A manages B manages A), use UNION instead of UNION ALL, carry a depth counter and stop at a limit, or keep a path column and refuse to revisit a node.
  • Track depth and path. Adding a depth column and, for display, a path string such as parent_path || ' > ' || name makes the hierarchy easy to read and sort.
  • Direction matters. Starting at the root and joining children walks down the tree; starting at a leaf and joining parents walks up to the root.
  • The recursive member sees only the last round, not the entire accumulated result, and in the standard it may refer to the CTE only once, without aggregates over itself.
  • Row order is not guaranteed. Sort in the outer query.
  • Performance: each round is an ordinary join, so index the parent column (manager_id) used in the join.
  • The standard name is WITH RECURSIVE; Oracle and SQL Server omit the keyword.