Recursive CTEs use WITH RECURSIVE to iterate until no new rows are found. Use them to traverse tree structures like category hierarchies and org charts. Include a depth limit to prevent infinite loops.
Recursive Queries
-- Category tree
WITH RECURSIVE tree AS (
-- Base case: root categories
SELECT id, name, parent_id, 0 AS depth
FROM categories WHERE parent_id IS NULL
UNION ALL
-- Recursive step
SELECT c.id, c.name, c.parent_id, t.depth + 1
FROM categories c
JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree ORDER BY depth, name;