Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

Part 3 - How does a recursive CTE work

Suggested Videos:
Part 1 - How to find nth highest salary in sql
Part 2 - SQL query to get organization hierarchy



A lot of you have asked to explain, how a recursive CTE work line by line. If you have not watched Part 2 already, I strongly recommend to watch that video first before proceeding.



In Part 2 of SQL Server Interview questions and answers video series we discussed recursive CTE to retrieve the organization hierarchy.

We will be using Employees table to understand how a recursive CTE works.
sql server query to display org chart

Here is the recursive CTE that gets the organization hierarchy based on an EmployeeId. Notice that the recursive CTE has got 2 parts (Anchor & Recursive Member)
Declare @ID int;
Set @ID = 7;

WITH EmployeeCTE AS
(
     -- Anchor
     Select EmployeeId, EmployeeName, ManagerID
     From Employees
     Where EmployeeId = @ID
    
     UNION ALL
    
     -- Recursive Member
     Select Employees.EmployeeId , Employees.EmployeeName, Employees.ManagerID
     From Employees
     JOIN EmployeeCTE
     ON Employees.EmployeeId = EmployeeCTE.ManagerID
)

Select E1.EmployeeName, ISNULL(E2.EmployeeName, 'No Boss') as ManagerName
From EmployeeCTE E1
LEFT Join EmployeeCTE E2
ON E1.ManagerID = E2.EmployeeId

When David's EmployeeId is passed the query produces the following output
how does a recursive cte work

Let's now discuss how the CTE executes line by line.
Step 1: Execute the anchor part and get result R0
Step 2: Execute the recursive member using R0 as input and generate result R1
Step 3: Execute the recursive member using R1 as input and generate result R2
Step 4: Recursion goes on until the recursive member output becomes NULL
Step 5: Finally apply UNION ALL on all the results to produce the final output

sql server interview questions and answers

2 comments:

  1. Hi Venkat,
    Nice explanation on Recursive CTE. I am curious about another way to solve the same. Pls suggest, if any.

    ReplyDelete
  2. if someone ask me that, what is a recursive query? in simple what is the answer for this question? kindly reply

    ReplyDelete

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