Recursive CTEs
A recursive CTE repeats a query on its own output, which is how you walk trees and generate sequences.
In this page:
Syntax
WITH RECURSIVE cte_name AS (
SELECT columns FROM table_name WHERE anchor_condition
UNION ALL
SELECT columns FROM table_name t JOIN cte_name c ON t.parent_id = c.id
)
SELECT * FROM cte_name;Recursive CTEs
WITH RECURSIVE has an anchor query (the starting rows), UNION ALL, and a recursive query that references the CTE itself.
It stops when the recursive part returns no new rows. Use it for hierarchies such as organisation charts or category trees.
Guard against infinite loops with a depth limit.
Note:
Add a depth column or LIMIT to protect against cycles.
Example: Recursive CTEs
CREATE TABLE staff (id INTEGER PRIMARY KEY, name TEXT, manager_id INTEGER);
INSERT INTO staff VALUES (1,'Ada',NULL),(2,'Bob',1),(3,'Cy',1),(4,'Di',2),(5,'Ed',4);
WITH RECURSIVE chain(id, name, depth) AS (
SELECT id, name, 0 FROM staff WHERE manager_id IS NULL
UNION ALL
SELECT s.id, s.name, c.depth + 1 FROM staff s JOIN chain c ON s.manager_id = c.id
)
SELECT name, depth FROM chain ORDER BY depth, name;
WITH RECURSIVE counter(n) AS (SELECT 1 UNION ALL SELECT n + 1 FROM counter WHERE n < 5)
SELECT n FROM counter;
-- Output:
-- name | depth
-- Ada | 0
-- Bob | 1
-- Cy | 1
-- Di | 2
-- Ed | 3
-- n
-- 1
-- 2
-- 3
-- 4
-- 5Login to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Forgetting the termination condition
- Cycles in the data creating infinite loops
- Using UNION instead of UNION ALL unnecessarily
Chapter Summary
- Anchor plus recursive term
- UNION ALL combines them
- Stops when no new rows appear
- Ideal for hierarchies
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: