Recursive CTE for hierarchical data in SQL

Introduction

Recursive CTEs help us find and show data that is connected in a chain, like family trees or company departments.

When you want to find all employees under a manager in a company.

When you need to show folder and file structures inside a computer.

When you want to list all parts that make up a product, including parts of parts.

When you want to find all replies in a comment thread on a website.

Syntax

SQL

WITH RECURSIVE cte_name (column_list) AS (
  -- Anchor member: select the starting rows
  SELECT columns
  FROM table
  WHERE condition
  
  UNION ALL
  
  -- Recursive member: select rows that connect to previous rows
  SELECT columns
  FROM table
  JOIN cte_name ON join_condition
)
SELECT * FROM cte_name;

The anchor member is the starting point of the recursion.

The recursive member repeats, adding connected rows until no more are found.

Examples

Finds all employees starting from top managers (no manager) and adds their subordinates level by level.

SQL

WITH RECURSIVE EmployeeHierarchy (EmployeeID, ManagerID, Level) AS (
  SELECT EmployeeID, ManagerID, 1
  FROM Employees
  WHERE ManagerID IS NULL
  
  UNION ALL
  
  SELECT e.EmployeeID, e.ManagerID, eh.Level + 1
  FROM Employees e
  JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

Builds full folder paths starting from root folders.

SQL

WITH RECURSIVE FolderTree (FolderID, ParentFolderID, Path) AS (
  SELECT FolderID, ParentFolderID, CAST(FolderName AS VARCHAR(100))
  FROM Folders
  WHERE ParentFolderID IS NULL
  
  UNION ALL
  
  SELECT f.FolderID, f.ParentFolderID, CONCAT(ft.Path, '/', f.FolderName)
  FROM Folders f
  JOIN FolderTree ft ON f.ParentFolderID = ft.FolderID
)
SELECT * FROM FolderTree;

Sample Program

This program creates an Employees table, inserts sample data, and uses a recursive CTE to find all employees with their levels in the hierarchy starting from top managers.

SQL

CREATE TABLE Employees (
  EmployeeID INT PRIMARY KEY,
  EmployeeName VARCHAR(50),
  ManagerID INT
);

INSERT INTO Employees (EmployeeID, EmployeeName, ManagerID) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eva', 2),
(6, 'Frank', 3);

WITH RECURSIVE EmployeeHierarchy (EmployeeID, EmployeeName, ManagerID, Level) AS (
  SELECT EmployeeID, EmployeeName, ManagerID, 1
  FROM Employees
  WHERE ManagerID IS NULL
  
  UNION ALL
  
  SELECT e.EmployeeID, e.EmployeeName, e.ManagerID, eh.Level + 1
  FROM Employees e
  JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT EmployeeID, EmployeeName, ManagerID, Level FROM EmployeeHierarchy ORDER BY Level, EmployeeID;

Important Notes

Recursive CTEs run until no new rows are added, so make sure your join condition eventually stops adding rows.

They are useful for trees and graphs stored in tables.

Watch out for cycles in data; they can cause infinite loops.

Summary

Recursive CTEs let you query hierarchical or connected data step by step.

Start with a base query, then add connected rows repeatedly.

Use them to explore trees like org charts, folder structures, or parts lists.