recursive_cte_hierarchy

🌀 Recursive CTE for Hierarchical Queries

Dive into recursive Common Table Expressions (CTEs) to efficiently traverse tree-like data such as organizational structures or category hierarchies. Recursive CTEs allow you to build queries that reference their own output, making hierarchical queries concise and performant. Use them to fetch entire subtrees in a single query without multiple round-trips.

WITH RECURSIVE subordinates AS (
  SELECT id, manager_id, name
  FROM employees
  WHERE manager_id IS NULL  -- root nodes
  UNION ALL
  SELECT e.id, e.manager_id, e.name
  FROM employees e
  JOIN subordinates s ON e.manager_id = s.id
)
SELECT * FROM subordinates;