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.
WITH RECURSIVE countdown(n) AS ( SELECT 3 UNION ALL SELECT n - 1 FROM countdown WHERE n > 1 ) SELECT n FROM countdown;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
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 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 < 5guard, or reaching the leaves of a tree), or you bound the output withLIMIT. - Cycles loop forever. If the data can contain loops (A manages B manages A), use
UNIONinstead ofUNION ALL, carry adepthcounter and stop at a limit, or keep a path column and refuse to revisit a node. - Track depth and path. Adding a
depthcolumn and, for display, a path string such asparent_path || ' > ' || namemakes 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.
Related
WITH(CTEs),UNION,INNER JOIN,SELECT- Lessons: Recursive CTEs, CTEs, Self join