Recursive CTEs (Hierarchical Queries) in SQL

🔹 What Is a Recursive CTE?

A Recursive Common Table Expression (CTE) allows a query to reference itself. It is perfect for traversing hierarchical data such as manager → employee, parent → child, or folder → subfolder relationships without procedural loops.

⚙️ Syntax Structure

WITH RECURSIVE cte_name AS (
  -- Anchor member (base rows)
  SELECT ...
  FROM table
  WHERE condition

  UNION ALL

  -- Recursive member (references cte_name)
  SELECT ...
  FROM table t
  JOIN cte_name c ON t.parent_id = c.id
)
SELECT *
FROM cte_name;

• Anchor member: returns the starting point (root rows).
• Recursive member: references the CTE to fetch the next level.
• UNION ALL: appends each level; use UNION only if you must remove duplicates.
• Termination: recursion stops automatically when no new rows are returned.

🧠 Example 1 – Employee Hierarchy

WITH RECURSIVE EmployeeHierarchy AS (
  -- Anchor: top-level manager
  SELECT employee_id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- Recursive: direct reports
  SELECT e.employee_id, e.name, e.manager_id, eh.level + 1
  FROM employees e
  INNER JOIN EmployeeHierarchy eh
    ON e.manager_id = eh.employee_id
)
SELECT *
FROM EmployeeHierarchy
ORDER BY level, name;

🧠 Example 2 – Category Tree

WITH RECURSIVE CategoryTree AS (
  SELECT category_id, category_name, parent_id, 1 AS level
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  SELECT c.category_id, c.category_name, c.parent_id, ct.level + 1
  FROM categories c
  JOIN CategoryTree ct ON c.parent_id = ct.category_id
)
SELECT *
FROM CategoryTree;

⚙️ Example 3 – Summing Hierarchical Values

WITH RECURSIVE SalesCTE AS (
  SELECT region_id, sales, parent_region_id
  FROM regions
  WHERE parent_region_id IS NULL

  UNION ALL

  SELECT r.region_id, r.sales, r.parent_region_id
  FROM regions r
  JOIN SalesCTE s ON r.parent_region_id = s.region_id
)
SELECT parent_region_id, SUM(sales) AS total_sales
FROM SalesCTE
GROUP BY parent_region_id;

⚖️ Key Benefits

• Simplifies hierarchical traversals without loops.
• Works across major RDBMS engines (PostgreSQL, SQL Server, Oracle, MySQL 8+).
• Lets you add depth counters or breadcrumbs easily.
• Can be combined with window functions for advanced analytics.

⚠️ Safety Tips

• Use UNION ALL unless you absolutely need duplicate removal.
• Add safeguards (e.g., WHERE level <= 10 or OPTION (MAXRECURSION 10) in SQL Server) to avoid infinite loops.
• Ensure parent-child relationships are indexed for performance.

💡 Interview Soundbite