Recursive Common Table Expressions (CTEs) are one of the most powerful features in SQL for handling hierarchical or graph-structured data. Whether you are dealing with employee-manager chains, bill of materials (BOM), or category trees, recursive CTEs allow you to walk parent-child relationships efficiently.

Understanding the Anatomy of a Recursive CTE

A Recursive CTE consists of three key parts:

  1. Anchor Member: The initial query that defines the base case.
  2. Recursive Member: The query that references the CTE itself to fetch child rows.
  3. Termination Condition: Implicitly achieved when no additional rows are returned.
WITH RECURSIVE org_hierarchy AS (
  -- 1. Anchor Member: Find top-level managers (CEO)
  SELECT 
    employee_id,
    name,
    manager_id,
    1 AS level,
    CAST(name AS VARCHAR(1000)) AS path
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- 2. Recursive Member: Join employees with org_hierarchy
  SELECT 
    e.employee_id,
    e.name,
    e.manager_id,
    h.level + 1,
    CAST(h.path || ' -> ' || e.name AS VARCHAR(1000))
  FROM employees e
  INNER JOIN org_hierarchy h ON e.manager_id = h.employee_id
)
SELECT * FROM org_hierarchy
ORDER BY level, employee_id;

Performance & Optimization Best Practices

Warning: Without proper indexing on foreign key joins (manager_id), recursive queries can trigger costly full table scans.