How Can a Recursive Self-Join in SQL Server Be Used to Retrieve an Employee Hierarchy?

SQL Server Recursive Self-Join for Employee Hierarchy

A recursive self-join in SQL Server is a powerful technique for navigating hierarchical data structures. Let's illustrate this using an employee table where each employee has a unique ID and the ID of their supervisor:

Example Employee Table:

<code>| EmployeeID | Name       | ManagerID |
|------------|------------|------------|
| 1          | John Doe   | NULL       |
| 2          | Jane Smith | 1          |
| 3          | Mary Jones | 2          |
| 4          | Bob Johnson| 3          |</code>

The Challenge:

Our goal is to retrieve the complete employee hierarchy under a specific manager (e.g., John Doe). A recursive self-join provides an elegant solution.

The Solution:

We can achieve this using the WITH statement (Common Table Expression or CTE):

<code class="language-sql">WITH EmployeeHierarchy AS (
  SELECT
    EmployeeID,
    Name,
    ManagerID
  FROM
    employees
  WHERE
    ManagerID IS NULL  -- Start with the top-level employee (no manager)
  UNION ALL
  SELECT
    e.EmployeeID,
    e.Name,
    e.ManagerID
  FROM
    employees e
  JOIN
    EmployeeHierarchy q
    ON e.ManagerID = q.EmployeeID  -- Recursively join to itself
)
SELECT
  *
FROM
  EmployeeHierarchy;</code>

This query begins with the root employee (no manager) and recursively joins the employees table to the CTE (EmployeeHierarchy), expanding the hierarchy level by level until all subordinates are included.

Maintaining Hierarchical Order:

To maintain the correct hierarchical order, we can enhance the query:

<code class="language-sql">WITH EmployeeHierarchy AS (
  SELECT
    EmployeeID,
    Name,
    ManagerID,
    CAST(ROW_NUMBER() OVER (ORDER BY EmployeeID) AS VARCHAR(MAX)) AS HierarchyPath
  FROM
    employees
  WHERE
    ManagerID IS NULL
  UNION ALL
  SELECT
    e.EmployeeID,
    e.Name,
    e.ManagerID,
    q.HierarchyPath + '.' + CAST(ROW_NUMBER() OVER (PARTITION BY e.ManagerID ORDER BY e.EmployeeID) AS VARCHAR(MAX))
  FROM
    employees e
  JOIN
    EmployeeHierarchy q
    ON e.ManagerID = q.EmployeeID
)
SELECT
  *
FROM
  EmployeeHierarchy
ORDER BY
  HierarchyPath;</code>

This improved query adds a HierarchyPath column, creating a unique path for each employee, ensuring proper hierarchical sorting in the final output. The ROW_NUMBER() function and string concatenation maintain the parent-child relationships.

The above is the detailed content of How Can a Recursive Self-Join in SQL Server Be Used to Retrieve an Employee Hierarchy?. For more information, please follow other related articles on the PHP Chinese website!