Often we use self referencing hierarchical table in database. Typical example is an employee table which has an employee in a row and every employee has a supervisor who is another employee. Thus the employee chain may have a chain.

Another example is a geographical hierarchy where the lowest level geography could be country. Group of several countries form a region.

Several regions together form a continent and finally all the continents form the total world. In such self referencing hierarchical table, we often need to answer the following queries.

1. What are the countries that form a specific continent?
2. In which region a country belongs?
3. In which continent a country belongs?
4. What is the upper level employee chain for a specific employee?
5. Who are the sub ordinates of a specific employee starting from his next level to the lowest level?

And many more. Often these questions need loops of query. Typically we use cursor for these looping. But recursive CTE provides us a very smart way of answering these queries. We will consider the following employee hierarchy in the below discussion. We will consider a table named Employees that holds this data. We have 3 columns: empid (Employee Id), mgrid (Manager Id) and empname (Employee Name).

Finding all the employees subordinate to one specific employee:

Suppose we want to find out all the employees under Janet. Here we have a ragged tree. Just below Janet we have 3 employees. Only one of them, Robert, has more subordinates. Again, Dan has another level of subordinate. We want to find out all the members below Janet. A typical solution could be using loop and a table variable or a temporary table. We must use t-sql stored procedure if we don't use CTE.

declare @employeeTable table

(

empid int,

mgrid int,

empname varchar(100),

position int

)

declare @depth int= 0, @count int= 1

insertinto @employeeTable

select empid, mgrid, empname, @depth

from Employees

where empid = 3

while @count != 0

begin

insertinto @employeeTable

select A.empid, A.mgrid, A.empname, @depth+1

from Employees A, @employeeTable B

where B.position = @depth

and A.mgrid = B.empid

select @count = COUNT(*)

from @employeeTable

where position = @depth+1

set @depth = @depth + 1

end

select*

from @employeeTable

where position != 0

This will give us the desired result. But the algorithm is already complex. Let's first see how this algorithm works.

1. We are inserting the specific employee Janet for whom we want to find out the subordinates. In our case the id of Janet is 3. We set the position of Janet as 0. She is the top order employee for our query.
2. Next we find out the employees who have Janet directly as their manager. The position for these employees will be 1. Then we find out whether we found any number of employees at this position. The @count variable holds the value.
3. In the next iteration we are taking the employees at position 1 from our table variable and finding out the employees who have any one of these employees as their manager. We insert them as employees of position 2.
4. We go on looping this way until we find out any new employees. If no additional employee is inserted at a iteration, we stop the loop.

Then our desired employees will be all the employees in the @employeeTable except Janet. Doing the same thing using recursive CTE: In fact what we have done here is almost what the recursive CTE does. But the good thing is we don't need to maintain the table variable, we don't need to maintain the depth and the condition for terminating the loop. Recursive CTE will itself do it for us and of course in a smarter way.

;with cte as (

select empid, mgrid, empname

from dbo.Employees

where mgrid = 3

unionall

select A.empid, A.mgrid, A.empname

from Employees A, cte B

where A.mgrid = B.empid

)

select*

from cte

Yes, this is all what we need to do to get our result. Now let's see how the recursive CTE is doing this and when it will stop. The first statement is the starting point of the CTE.

select*

from dbo.Employees

where mgrid = 3

When this is executing, we are getting the employees who are direct subordinate of Janet. This statement is executed only once at the starting of the recursive CTE. So our cte table will contain the following rows.

The next statement below the union all will be executed recursively until this second statement finds any new rows for the cte.

select A.empid, A.mgrid, A.empname

from Employees A, cte B

where A.mgrid = B.empid

Here we are using cte table. Every time cte table will consider the rows it is getting from the previously executed statement. At this stage cte table will be considered to have the rows from the first statement. If any new row results from this second query, they will be considered as the rows of cte table for the next iteration. So at the first iteration, this statement will return the employees who are direct subordinate of Robert, Laura and Ann. This will return 3 new rows.

So in the next iteration, these 3 rows will be considered as rows of cte table. While joining with Employees table, it will now generate 1 new row at the second iteration which is subordinate of David.

In the third iteration, this row will be considered as cte table and it not find any new row as we don't have any subordinate for james. So no new row is generated. Now sqlserver knows that the cte table will no longer be updated. So it terminates the recursion. Now the final output will be the union of all the rows that has been generated. So the final table cte will contain the following rows.

In the next post we will see another query that we can serve using recursive CTE.