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.
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.
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.
ReplyDeleteYes 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
ReplyDeleteand 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.
Hello,
ReplyDeleteCan anyone please tell how to achieve the same result using other ways than CTE
sir i confused in increasing the levels by
ReplyDeleteEmployeesCTE.[Level] + 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,
DeleteFor 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
sir when we add +1 it becomes 1 2 3 4 5 6....
ReplyDeletehow it becomes 1 2 2 3 3 ...
please any one explain the logic behind that
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
DeleteWith
ReplyDeleteEmployeesCTE
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.
Hi,
ReplyDeleteIn 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
Please explain the other method to implement the same result.It will be very grateful!!!
ReplyDelete1.Select Employee whose MgrId is NULL, then
ReplyDeleteUNION 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
Can you pls provide with the working sql query of the steps you mentioned.
DeleteI tried the tutorial's query but its not working for some reason in sql redash.