SQLsqlrecursive-ctehierarchicalgraph-traversalproduction-ready
Recursive CTE Patterns
Recursive Common Table Expressions for hierarchical data, graph traversal, date series generation, and organizational trees.
title: "Recursive CTE Patterns" description: "Recursive Common Table Expressions for hierarchical data, graph traversal, date series generation, and organizational trees." category: "SQL" language: "sql" tags: ["sql", "recursive-cte", "hierarchical", "graph-traversal", "production-ready"] createdAt: "2025-03-05" updatedAt: "2025-03-25"
Overview
Recursive CTEs enable traversal of hierarchical and graph-structured data directly in SQL. They consist of a base case (anchor) and a recursive case that references itself, iterating until no new rows are produced.
Code
WITH RECURSIVE tree AS (
SELECT id, parent_id, 1 AS depth FROM nodes WHERE parent_id IS NULL
UNION ALL
SELECT n.id, n.parent_id, t.depth + 1
FROM nodes n JOIN tree t ON n.parent_id = t.id
)
SELECT * FROM tree;Notes
- Always include a depth/cycle guard (
WHERE depth < Nor visited array) to prevent infinite loops - The
visitedarray pattern detects cycles in graph structures UNION ALLis required (notUNION) for recursive CTEs in most databases- Date series generation fills gaps in sparse time-series data for accurate charts
- Performance tip: index the join column (parent_id, manager_id) for recursive traversal