Common Table Expressions (CTEs) in SQL

The Employee Hierarchy Problem

Imagine you are working with an employee database where each employee might report to a manager. Here's a sample table structure and data:

DECLARE @Employees TABLE 
( 
  EmployeeID int NOT NULL PRIMARY KEY, 
  FirstName varchar(50) NOT NULL, 
  LastName varchar(50) NOT NULL, 
  ManagerID int NULL 
)

INSERT INTO @Employees VALUES 
(1, 'Ken', 'Thompson', NULL), 
(2, 'Terri', 'Ryan', 1), 
(3, 'Robert', 'Durello', 1), 
(4, 'Rob', 'Bailey', 2), 
(5, 'Kent', 'Erickson', 2), 
(6, 'Bill', 'Goldberg', 3), 
(7, 'Ryan', 'Miller', 3), 
(8, 'Dane', 'Mark', 5), 
(9, 'Charles', 'Matthew', 6), 
(10, 'Michael', 'Jhonson', 6)

This table represents employees with their unique IDs (EmployeeID), names (FirstName and LastName), and their manager's ID (ManagerID).

Problem Statement

We want to retrieve the hierarchy of employees, displaying each employee along with their reporting level in the organization.

Solution: Recursive CTE

To solve this, we use a recursive CTE. Here's how it works:

1. Anchor Query: The base query selects the top-level employees (those with no managers).
2. Recursive Query: The recursive query fetches employees who report to the ones already processed, incrementing the hierarchy level.

Below is the implementation:

;WITH 
  cteReports (EmpID, FirstName, LastName, MgrID, EmpLevel) 
  AS 
  ( 
    -- Anchor query: Get top-level employees (managers with no manager)
    SELECT EmployeeID, FirstName, LastName, ManagerID, 1 
    FROM @Employees 
    WHERE ManagerID IS NULL 

    UNION ALL 

    -- Recursive query: Fetch employees reporting to the current level
    SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,  
           r.EmpLevel + 1 
    FROM @Employees e 
    INNER JOIN cteReports r 
      ON e.ManagerID = r.EmpID 
  ) 
-- Final query: Retrieve the complete hierarchy
SELECT * FROM cteReports

How It Works

1. Step 1: Anchor Query

• Finds employees without a manager (ManagerID IS NULL).
• Example result: Ken Thompson (Level 1).

1. Step 2: Recursive Query

• For each employee in the current hierarchy, it finds direct reports (e.ManagerID = r.EmpID).
• Adds them to the result set with an incremented level.

1. Step 3: Union All

• Combines the anchor and recursive query results into a single result set.

1. Step 4: Final Query

• Outputs the hierarchy of employees with their levels.

Output

Here’s what the query returns:

Key Insights

1. Hierarchy Visualization: Recursive CTEs simplify navigating hierarchical data.
2. Custom Levels: The EmpLevel column provides a clear idea of reporting levels.
3. Reusability: Recursive CTEs can handle complex structures like file directories or company hierarchies.

Practical Applications

• Organizational Charts: Visualize employee-manager relationships.
• File Systems: Represent folder-file structures.
• Bill of Materials (BOM): Navigate product assembly hierarchies.

Conclusion

Recursive CTEs are a versatile SQL tool for solving hierarchical data problems. By understanding how to build and apply them, you can handle complex data structures efficiently. Experiment with the provided example, and apply it to your projects to see the magic of recursive CTEs in action!