Recursive CTE - Part 51

Suggested SQL Server videos
Part 49 - Common Table Expressions (CTE)
Part 50 - Updatable CTE







A CTE that references itself is called as recursive CTE. Recursive CTE's can be of great help when displaying hierarchical data. Example, displaying employees in an organization hierarchy. A simple organization chart is shown below.


Let's create tblEmployee table, which holds the data, that's in the organization chart.
Create Table tblEmployee
(
  EmployeeId int Primary key,
  Name nvarchar(20),
  ManagerId int
)

Insert into tblEmployee values (1, 'Tom', 2)
Insert into tblEmployee values (2, 'Josh', null)
Insert into tblEmployee values (3, 'Mike', 2)
Insert into tblEmployee values (4, 'John', 3)
Insert into tblEmployee values (5, 'Pam', 1)
Insert into tblEmployee values (6, 'Mary', 3)
Insert into tblEmployee values (7, 'James', 1)
Insert into tblEmployee values (8, 'Sam', 5)
Insert into tblEmployee values (9, 'Simon', 1)

Since, a MANAGER is also an EMPLOYEE, both manager and employee details are stored in tblEmployee table. Data from tblEmployee is shown below.


Let's say, we want to display, EmployeeName along with their ManagerName. The ouptut should be as shown below.


To achieve this, we can simply join tblEmployee with itself. Joining a table with itself is called as self join. We discussed about Self Joins in Part 14 of this video series. In the output, notice that since JOSH does not have a Manager, we are displaying 'Super Boss', instead of NULL. We used IsNull(), function to replace NULL with 'Super Boss'. If you want to learn more about replacing NULL values, please watch Part 15.
SELF JOIN QUERY:
Select Employee.Name as [Employee Name],
IsNull(Manager.Name, 'Super Boss') as [Manager Name]
from tblEmployee Employee
left join tblEmployee Manager
on Employee.ManagerId = Manager.EmployeeId

Along with Employee and their Manager name, we also want to display their level in the organization. The output should be as shown below.


We can easily achieve this using a self referencing CTE.
With
  EmployeesCTE (EmployeeId, Name, ManagerId, [Level])
  as
  (
    Select EmployeeId, Name, ManagerId, 1
    from tblEmployee
    where ManagerId is null
    
    union all
    
    Select tblEmployee.EmployeeId, tblEmployee.Name, 
    tblEmployee.ManagerId, EmployeesCTE.[Level] + 1
    from tblEmployee
    join EmployeesCTE
    on tblEmployee.ManagerID = EmployeesCTE.EmployeeId
  )
Select EmpCTE.Name as Employee, Isnull(MgrCTE.Name, 'Super Boss') as Manager, 
EmpCTE.[Level] 
from EmployeesCTE EmpCTE
left join EmployeesCTE MgrCTE
on EmpCTE.ManagerId = MgrCTE.EmployeeId

The EmployeesCTE contains 2 queries with UNION ALL operator. The first query selects the EmployeeId, Name, ManagerId, and 1 as the level from tblEmployee where ManagerId is NULL. So, here we are giving a LEVEL = 1 for super boss (Whose Manager Id is NULL). In the second query, we are joining tblEmployee with EmployeesCTE itself, which allows us to loop thru the hierarchy. Finally to get the reuired output, we are joining EmployeesCTE with itself. 

12 comments:

  1. Your video's are excellent and meaningful. Thanks so much for so easy understanding tutorial but in this page I think left join is easy to use for this query instead of CTE. Left join query is shorter to write than writing this recursive CTE which even use the same left join.

    ReplyDelete
  2. Yes my friend I agree with you, but the Query used is only an example to show you how recursive CTE works, you can also achieve the same using other ways

    and yeah thank you Venkut I like this tutorials, but you said that we are going to be talking about Database Schema in a latter session.

    ReplyDelete
  3. Hello,
    Can anyone please tell how to achieve the same result using other ways than CTE

    ReplyDelete
  4. sir i confused in increasing the levels by

    EmployeesCTE.[Level] + 1

    ReplyDelete
    Replies
    1. In the first table you can see every one is having manager id's except JOSH, so here we are assuming JOSH as superboss and id as 1,
      For the remaining employees you already have managers id. hence we are retrieving the existing data from the table,
      Here level+1 is only telling you that select the JOSH as superboss with id =1 and add this data with
      existing remaining employees with id's

      Delete
  5. sir when we add +1 it becomes 1 2 3 4 5 6....
    how it becomes 1 2 2 3 3 ...
    please any one explain the logic behind that

    ReplyDelete
    Replies
    1. I don't know if this will help you out but it's something I noticed, but still even I'm looking for an explanation with regards to the same aspect, anyway what I've noticed is, is that the level value is only incremented when the manager ID for the employees change, so if you and I had a manager ID of 2 we'd both be on level 2 because the boss' id is two but when someone else has an ID that references our employee ID's(as manager Id's) then their level would be 3, but I can't understand why it increments ONLY when the manager ID changes and not for every value

      Delete
  6. With
    EmployeesCTE
    as
    (
    Select EmployeeId, Name, ManagerId, 1 as [Level]
    from tblEmployee
    where ManagerId is null
    ),
    EmployeesCTE1 as
    (
    Select tblEmployee.EmployeeId, tblEmployee.Name,
    tblEmployee.ManagerId, EmployeesCTE.[Level] + 1 as [Level]
    from tblEmployee
    join EmployeesCTE
    on tblEmployee.ManagerID = EmployeesCTE.EmployeeId
    ),
    EmployeesCTE2 as
    (
    Select tblEmployee.EmployeeId, tblEmployee.Name,
    tblEmployee.ManagerId, EmployeesCTE1.[Level] + 1 as [Level]
    from tblEmployee
    join EmployeesCTE1
    on tblEmployee.ManagerID = EmployeesCTE1.EmployeeId
    ),
    EmployeesCTE3 as
    (
    Select tblEmployee.EmployeeId, tblEmployee.Name,
    tblEmployee.ManagerId, EmployeesCTE2.[Level] + 1 as [Level]
    from tblEmployee
    join EmployeesCTE2
    on tblEmployee.ManagerID = EmployeesCTE2.EmployeeId
    ),
    EmployeesCTE4 as
    (
    Select tblEmployee.EmployeeId, tblEmployee.Name,
    tblEmployee.ManagerId, EmployeesCTE3.[Level] + 1 as [Level]
    from tblEmployee
    join EmployeesCTE3
    on tblEmployee.ManagerID = EmployeesCTE3.EmployeeId
    ),
    EmployeesCTEFinal as
    (SELECT *
    FROM EmployeesCTE
    UNION ALL
    SELECT *
    FROM EmployeesCTE1
    UNION ALL
    SELECT *
    FROM EmployeesCTE2
    UNION ALL
    SELECT *
    FROM EmployeesCTE3
    UNION ALL
    SELECT *
    FROM EmployeesCTE4)

    select emp.name,isnull(mrg.Name,'super boss') as ManagernName,emp.[level] from EmployeesCTEFinal emp
    left join EmployeesCTEFinal mrg on mrg.Employeeid = emp.ManagerId

    like above query is going to excute...
    Since EmployeesCTE4 yields no results, this implies a stopping condition.

    Theoretically, a recursive CTE can be infinite, but practically, SQL Server tries to forbid the queries that would lead to infinite recordsets.

    ReplyDelete
  7. Hi,
    In the query..

    Select Employee.Name as [Employee Name],
    IsNull(Manager.Name, 'Super Boss') as [Manager Name]
    from tblEmployee Employee
    left join tblEmployee Manager
    on Employee.ManagerId = Manager.EmployeeId

    If I change last line from
    on Employee.ManagerId = Manager.EmployeeId
    to
    on Employee.EmployeeId= Manager.ManagerId

    Then result is very different. please explain.

    Thanks in advance.
    Himanshu

    ReplyDelete
  8. Please explain the other method to implement the same result.It will be very grateful!!!

    ReplyDelete
  9. 1.Select Employee whose MgrId is NULL, then
    UNION ALL
    -----
    2.Select EmpID of row with MgrID(NULL), lookup in same table for which employees he is manager,add +1 to level and display(Here level 2 employees will be displayed)

    3.Next retrieve employeeIds displayed in level 2 , find out for whom they are managers, then display those employees details and add +1 to their level(2+1=3)

    3rd step continues...for all rows

    ReplyDelete
    Replies
    1. Can you pls provide with the working sql query of the steps you mentioned.
      I tried the tutorial's query but its not working for some reason in sql redash.

      Delete

It would be great if you can help share these free resources