Recursion in SQL: Unlocking Trees and Graphs with Recursive CTEs

Recursion in SQL: Unlocking Trees and Graphs with Recursive CTEs

"When your data forms a tree, SQL recursion lets you climb it — level by level, branch by branch."

Hierarchical data is everywhere: categories, organization charts, dependencies, and more. But handling recursive relationships in SQL can feel intimidating — until you meet recursive CTEs.

In this article, you’ll learn how to:

• Model a self-referencing table (e.g., Employees with ManagerID)
• Build a recursive Common Table Expression (CTE)
• Control recursion depth
• Format trees using depth indicators or breadcrumbs
• Optimize performance for hierarchy traversal

Let’s explore with a real-world use case: building an organizational chart.

Step 1: The Hierarchical Data Model

We’ll use a single table to model an org chart:

CREATE TABLE Employees (
  ID INT PRIMARY KEY,
  Name TEXT,
  ManagerID INT  -- self-referencing foreign key
);

INSERT INTO Employees (ID, Name, ManagerID) VALUES
(1, 'Alice (CEO)', NULL),
(2, 'Bob (CTO)', 1),
(3, 'Carol (CFO)', 1),
(4, 'Dan (Engineer)', 2),
(5, 'Eve (Engineer)', 2),
(6, 'Frank (Accountant)', 3);

Step 2: Write the Recursive CTE

WITH RECURSIVE OrgChart AS (
  -- Anchor query: the root (CEO)
  SELECT ID, Name, ManagerID, 1 AS Level, CAST(Name AS TEXT) AS Path
  FROM Employees
  WHERE ManagerID IS NULL

  UNION ALL

  -- Recursive part: find subordinates
  SELECT e.ID, e.Name, e.ManagerID, o.Level + 1,
         o.Path || ' > ' || e.Name
  FROM Employees e
  INNER JOIN OrgChart o ON e.ManagerID = o.ID
)
SELECT * FROM OrgChart ORDER BY Path;

What’s happening?

• We start with the top-level manager (ManagerID IS NULL)
• Each recursive step finds direct reports
• We build a Path field to show the full tree route (breadcrumb style)

✅ Output:

ID | Name             | Level | Path
---|------------------|-------|------------------------------
1  | Alice (CEO)      | 1     | Alice (CEO)
2  | Bob (CTO)        | 2     | Alice (CEO) > Bob (CTO)
4  | Dan (Engineer)   | 3     | Alice (CEO) > Bob (CTO) > Dan (Engineer)
5  | Eve (Engineer)   | 3     | Alice (CEO) > Bob (CTO) > Eve (Engineer)
3  | Carol (CFO)      | 2     | Alice (CEO) > Carol (CFO)
6  | Frank (Accountant)| 3    | Alice (CEO) > Carol (CFO) > Frank (Accountant)

Step 3: Add a Recursion Depth Guard

Prevent infinite loops with a maximum depth:

WITH RECURSIVE OrgChartLimited AS (
  SELECT ID, Name, ManagerID, 1 AS Level FROM Employees WHERE ManagerID IS NULL
  UNION ALL
  SELECT e.ID, e.Name, e.ManagerID, o.Level + 1
  FROM Employees e
  JOIN OrgChartLimited o ON e.ManagerID = o.ID
  WHERE o.Level < 5  -- safeguard!
)
SELECT * FROM OrgChartLimited;

Bonus: Visualizing the Tree

Add indentation to format a classic text-style tree:

SELECT REPEAT('  ', Level - 1) || Name AS TreeLine
FROM OrgChart;

Output:

Alice (CEO)
  Bob (CTO)
    Dan (Engineer)
    Eve (Engineer)
  Carol (CFO)
    Frank (Accountant)

Recap: Key Recursive CTE Concepts

Final Thoughts

Recursive CTEs are a game-changer for SQL developers dealing with hierarchical or graph-based data. They help express logic declaratively, replacing procedural loops and manual code with clean, performant traversal.

"When you speak SQL fluently, even a tree listens."

Ready to level up? Try building:

• A breadcrumb generator for nested categories
• A dependency resolver for package managers
• A time-aware hierarchy with versioning

#SQL #CTE #Recursion #GraphQueries #PostgreSQL #SQLServer #AdvancedSQL #DataEngineering