Recursive CTE Example: Hierarchy of Employees

 Finding the Hierarchy of Employees

Imagine you have an employees table with the following structure:

sql
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100),
    manager_id INT
);

Here's a sample dataset:

sql
INSERT INTO employees (employee_id, employee_name, manager_id) VALUES
(1, 'Alice', NULL),
(2, 'Bob', 1),
(3, 'Charlie', 1),
(4, 'David', 2),
(5, 'Eve', 2),
(6, 'Frank', 3);

You want to find the hierarchy of all employees starting from a specific manager (say, Alice).

sql
WITH RECURSIVE EmployeeHierarchy AS (
    -- Anchor member: start with the root manager
    SELECT 
        employee_id,
        employee_name,
        manager_id,
        1 AS level
    FROM 
        employees
    WHERE 
        employee_name = 'Alice'

    UNION ALL

    -- Recursive member: find employees who report to the current employee
    SELECT 
        e.employee_id,
        e.employee_name,
        e.manager_id,
        eh.level + 1
    FROM 
        employees e
    INNER JOIN 
        EmployeeHierarchy eh ON e.manager_id = eh.employee_id
)
SELECT 
    employee_id,
    employee_name,
    manager_id,
    level
FROM 
    EmployeeHierarchy
ORDER BY 
    level, employee_id;

Recursive CTE Example: calculate the factorial of a number

Calculating the factorial of a number using a recursive CTE in SQL can be both an instructive and practical example. Here’s a detailed step-by-step guide to achieve this.

Step-by-Step Explanation

1. Anchor Member: The base case of the recursion. For the factorial calculation, the base case is 0! = 1 or 1! = 1.
2. Recursive Member: Defines the recursive step. For the factorial of n, we have n! = n * (n-1)!.

Here's the SQL code to calculate the factorial of a number (let's say 5):

SQL Code

sql
WITH RECURSIVE FactorialCTE AS (
    -- Anchor member: start with 1
    SELECT 
        1 AS n, 
        1 AS factorial
    UNION ALL
    -- Recursive member: calculate factorial for n + 1
    SELECT 
        n + 1,
        factorial * (n + 1)
    FROM 
        FactorialCTE
    WHERE 
        n < 5 --Set the Dynamic limit to the desired number, in this case, 5
)
SELECT 
    n, 
    factorial
FROM 
    FactorialCTE
ORDER BY 
    n;

Explanation

1. Anchor Member:

   sql
   SELECT 
       1 AS n, 
       1 AS factorial
   This initializes the recursion with 1! = 1.

2. Recursive Member:

   sql
   SELECT 
       n + 1,
       factorial * (n + 1)
   FROM 
       FactorialCTE
   WHERE 
       n < 5
   
   

   This part of the CTE calculates the factorial by multiplying the current value of factorial by n + 1 until n reaches 5.

3. Final Select:

   sql
   SELECT 
       n, 
       factorial
   FROM 
       FactorialCTE
   ORDER BY 
       n;
   
   

   This selects and orders the results by n.

Result

The result will be a table listing each n from 1 to 5 and the corresponding factorial value:

 n | factorial
---|-----------
 1 | 1
 2 | 2
 3 | 6
 4 | 24
 5 | 120