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 < N or visited array) to prevent infinite loops
  • The visited array pattern detects cycles in graph structures
  • UNION ALL is required (not UNION) 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