Recursive Common Table Expressions (CTEs)

Snippet

Recursive Common Table Expressions (CTEs)

Recursive CTEs are used to traverse hierarchical data structures like organizational charts or bill of materials. They consist of an anchor member and a recursive member joined by UNION ALL.

snippet.sql
sql
WITH RECURSIVE subordinates AS (
  SELECT employee_id, manager_id, name
  FROM employees
  WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, e.manager_id, e.name
  FROM employees e
  INNER JOIN subordinates s ON s.employee_id = e.manager_id
)
SELECT * FROM subordinates;

Breakdown

WITH RECURSIVE subordinates AS

Defines the CTE name and marks it as recursive.

WHERE manager_id IS NULL

The anchor member: selects the starting point (e.g., the CEO).

INNER JOIN subordinates s ON s.employee_id = e.manager_id

The recursive member: joins the table back to the CTE to find the next level of the hierarchy.

Previous snippet Next snippet

From your library

Recursive Common Table Expressions (CTEs)

Related