Recursive Queries using Common Table Expressions (CTE) in SQL Server

By:   |   Updated: 2021-10-06   |   Comments (18)   |   Related: > Common Table Expressions


We need a better way to implement recursive queries in SQL Server and in this article we look at how this can be done using a Common Table Expression or CTE.


Common Table Expression (CTE) was introduced in SQL Server 2005 and can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. You can think of CTE as an improved version of derived tables that more closely resemble a non-persistent type of view.

A CTE can be used in many of the same ways you use a derived table. CTEs can also contain references to themselves. This allows the developer to write complex queries simpler. CTEs can also be used in place of views. The use of CTEs provides two main advantages. One is that queries with derived table definitions become more simple and readable. While traditional T-SQL constructs that are used to work with derived tables normally requires a separate definition for the derived data such as a temporary table or a table-valued function, using CTEs make it easier to see the definition of the derived table with the code that uses it. The other thing is that CTEs significantly reduces the amount of code required for a query that traverses recursive hierarchies.

To understand what a CTE is all about, let's first take a look at the syntax to create it in SQL Server.


In general form a recursive CTE has the following syntax:

WITH cte_alias (column_aliases)  
cte_query_definition   --initialization 
cte_query_definition2 --recursive execution 
SELECT * FROM cte_alias 

You provide the CTE with an alias and an optional list of aliases for its result columns following the keyword WITH which usually defines the derived table based on the query definition; write the body of the CTE; and refer to it from the outer query.

Example CTE

To put this in the right perspective, let's come up with a simple example which uses recursion. We'll look at the Employees table in the Northwind database and see that a particular employee reports to another employee. One question we can come up with is, "Who reports to whom?" The Employees table is designed in such a way that the ReportsTo column is a foreign key field that refers to the primary key field EmployeeID. Thus, we can create a query to answer our question. A sample query using CTE will look something like this.

WITH Managers AS 
SELECT EmployeeID, LastName, ReportsTo  
FROM Employees 
--recursive execution 
SELECT e.employeeID,e.LastName, e.ReportsTo 
FROM Employees e INNER JOIN Managers m  
ON e.ReportsTo = m.employeeID 
SELECT * FROM Managers  

Code Walkthrough

  1. The recursive CTE, Managers, defines an initialization query and a recursive execution query
  2. The initialization query returns the base result and is the highest level in the hierarchy.  This is identified by the ReportsTo value of NULL, which means that the particular Employee does not report to anybody.  Depending on how the table is designed, the value can be anything as long as it represents the highest level in the hierarchy
  3. The recursive execution query is then joined to the initialization query using the UNION ALL keyword.  The result set is based on the direct subordinate as returned by the initialization query, which then appears as the next level in the hierarchy. Recursion occurs because of the query referencing the CTE itself based on the Employee in the Managers CTE as input. The join then returns the employees who have their managers as the previous record returned by the recursive query.  The recursive query is repeated until it returns an empty result set.
  4. The final result set is returned by querying the Managers CTE 

The sample query contains the elements that a recursive CTE must contain. What's more is that the code is a lot more readable. This enables the developers to write complex queries with ease.

Max Recursion

You can also use a query hint to stop a statement after a defined number of loops. This can stop a CTE from going into an infinite loop on a poorly coded statement. You do this by including the MAXRECURSION keyword in the SELECT query referring to the CTE. To use it in the previous example, just replace the last line with the line below.


Create Similar Result Without CTE

To create a similar yet non-recursive query that produces the same result, you might come up with something similar to the code below which is much more complex:

DECLARE @rowsAdded INT 

--table variable to hold accumulated results 
DECLARE @managers TABLE --initialize @managers who do not have managers 
   (EmpID INT, MgrID INT, LastName nvarchar(20), processed INT DEFAULT(0)) 

INSERT @managers  
SELECT EmployeeID, ReportsTo, LastName, 0  
FROM Employees  

SET @rowsAdded=@@rowcount  

--do this while new employees are added in the previous iteration  
WHILE @rowsAdded > 0  

   --mark employee records going to be found in this iteration with --processed=1  
   UPDATE @managers SET processed=1 WHERE processed=0 

   --insert employees who report to employees not yet processed  
   INSERT @managers  
   SELECT EmployeeID, ReportsTo, e.LastName, 0  
   FROM Employees e  
   INNER JOIN @managers r ON e.ReportsTo = r.EmpID  
   WHERE ReportsTo <> EmployeeID AND r.processed = 1  
   SET @rowsAdded = @@rowcount  

   --mark employee records found in this iteration as processed  
   UPDATE @managers SET processed=2 WHERE processed=1  


SELECT EmpID as EmployeeID, LastName, MgrID as ReportsTo FROM @managers 
Next Steps

Given the example above, hierarchical data structures, organizational charts and other parent-child table relationship reports can easily benefit from the use of recursive CTEs. CTEs bring us the chance to create much more complex queries while retaining a much simpler syntax. They also can lessen the administrative burden of creating and testing views for situations where the view will not be reused.

  • Learn more about using recursive CTEs from the MSDN Library
  • You can get the Northwind database used in the sample from the links here

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Edwin Sarmiento Edwin M Sarmiento is a Microsoft SQL Server MVP and Microsoft Certified Master from Ottawa, Canada specializing in high availability, disaster recovery and system infrastructures.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2021-10-06

Comments For This Article

Wednesday, January 16, 2019 - 10:02:34 AM - charles Back To Top (78787)

Genius Tutorial, work like a charm. 

Friday, March 20, 2015 - 3:01:57 AM - Noor Alam Back To Top (36599)

Thank You  sir........

Wednesday, March 18, 2015 - 2:28:35 AM - Binh Nguyen Thanh Back To Top (36568)

Thanks, nice post

Sunday, December 21, 2014 - 8:33:41 AM - laith dolat Back To Top (35714)

thank you very much

Friday, October 31, 2014 - 11:53:38 AM - Scott Back To Top (35137)

I think I would write SQL 2000 example without an UPDATE query, and make MAXRECURSION more obvious.

DECLARE @rowsAdded INT 
DECLARE @Iteration INT = 1;
DECLARE @MaxRecursion INT = 4;  -- Defaults to 100 if not specified

--table variable to hold accumulated results 
DECLARE @managers TABLE (EmpID INT, MgrID INT, Iteration INT) 

-- Anchor query
--initialize @managers who do not have managers 
INSERT @managers  
SELECT EmployeeID, ReportsTo, @Iteration
FROM Employees  
SET @rowsAdded=@@rowcount ;

-- Recursive query
-- Uses only the results from the previous iteration, and continues until no more rows added (or MAXRECURSION reached)
WHILE @rowsAdded > 0 AND @Iteration <= @MaxRecursion BEGIN
    INSERT @managers  
    SELECT EmployeeID, ReportsTo, @Iteration + 1  
    FROM Employees e  
    INNER JOIN @managers r ON e.ReportsTo = r.EmpID  
    WHERE ReportsTo <> EmployeeID AND r.Iteration = @Iteration;

    SET @rowsAdded=@@rowcount;
    SET @Iteration = @Iteration + 1;

Saturday, February 15, 2014 - 8:33:14 AM - Mohit Back To Top (29462)

I need to write much more complex queries of 1000 lines in CTE but i am unable to understand the scenario how to fetech all the data after end of my temp tables.Could someone help me on that?

Dta is all about of HR:

My data is one temp table for all the Hires data,2) temp table for all the Terminatins, 3) temp table for start data ,4) temp table end data,

I need to find tranfer in and Transfer out among the hire and end data.

How can I find out  transfer In and Transfer Out?


Please Help!!!!!!!!!!!!! 


Friday, October 11, 2013 - 9:26:32 AM - upen Back To Top (27119)


this was really simple and informative.



Tuesday, July 2, 2013 - 8:16:37 PM - Kristy Back To Top (25682)

"Select EmployeeID, LastName, ReportsTo from Employees" what is the difference between this and 

"WITH Managers AS --initialization ... "

query above?

I get the same result running on SQL 2012 database against NorthWnd database.

Thursday, March 21, 2013 - 5:21:41 PM - Tallion Back To Top (22948)
An example I was able to understand and put into action. I have one table (Inventory) with three columns (ID, Desc, ParentID) where the parentID links back to the ID. I store an item in house A (id=1, PID = null), Room B (ID=2, PID=1) box C (ID=3, PID=2) (house A > Room B > box C). So when i query the table i have the box C ID of 3. this allowed me to 'walk' up the chain to get room B and house A WITH InvItems AS ( SELECT a.ID ,a.Description , A.ParentLocationID from InventoryLocations A where a.ID = 6 UNION ALL SELECT b.ID,b.Description, b.ParentLocationID from InventoryLocations b INNER JOIN InvItems I ON b.ID = I.ParentLocationID ) SELECT * FROM InvItems returns ID Desc ParentD 3 box C 2 2 Room B 1 1 House A Null Thanks, Tallion

Tuesday, March 12, 2013 - 12:47:48 AM - Rashid Abbas Back To Top (22722)

WITH Managers AS 


  SELECT Employee_Id,Full_Name,SBT_Name, Manager_Id,1 LevelNumber

  From HRM.dbo.HR_Employee

  WHERE Manager_Id IS NULL 


  --recursive execution 

  SELECT e.Employee_Id,e.Full_Name,e.SBT_Name, e.Manager_Id,m.LevelNumber+1 LevelNumber 

  FROM HRM.dbo.HR_Employee e

  INNER JOIN Managers m  ON e.Manager_Id = m.Employee_Id 

SELECT * FROM Managers OPTION (MAXRECURSION 3)--Number should by three or more

Monday, February 18, 2013 - 7:33:13 AM - Ian Back To Top (22229)

Nice, very clear, I think the guy saying you could do it another way is missing the point. thanks

Monday, July 30, 2012 - 1:31:20 AM - Nikhilesh Patidar Back To Top (18841)


Thank you  for provide solution

Friday, May 18, 2012 - 12:10:53 AM - Abhi Back To Top (17515)

Suppose I have a table REL(ParentID int, ChildID int)

The values in the table are

11, 1






Given the input as 11, I wnat the output as




Logic: Starting with 11, the childIDs are 1,2.

Now ParentId having child id = 1,2 are 22.

Now ParentId 22 has childid as 1 and 4.

Again ParentID 44 has childis as 4.

So the output should be




 How can I acheive this? Please help.

Thursday, July 21, 2011 - 9:12:52 AM - Raj (SQL lover) Back To Top (14240)

One of the best example for CTE in SLQ Server 2008.

Good Job.



Wednesday, March 16, 2011 - 1:57:13 PM - Jim Davis Back To Top (13236)

Excellent example, it saves a lot of time over other methods to do the same thing. Much simpler than examples from MS. Personally, I really appreciate it when someone, like this writer, makes such an effort to explain how to do something like this. This will save everyone alot of time and effort.

Keep up the good work.

Jim Davis

Friday, January 7, 2011 - 8:19:46 PM - Randy Edge Back To Top (12536)

Thanks for the code.  You saved me a day or two of messing around.

As usual Microsofts example was overly complicated. It had joins to other tables which just make it harder to see what what going on.You did an excellent job.

Friday, July 18, 2008 - 5:42:16 AM - bass_player Back To Top (1449)

Hi Pro,

CURSORS and SELF JOINS can be used in the given example. It's just that CTEs would make it a lot simpler and easier to achieve the required task

Friday, July 18, 2008 - 2:35:46 AM - Pro Back To Top (1448)

Though the post is nice but the example that was taken here can be easily handles using a self join on the employer table and using curser

Can anyone please explain me why should we use CTE in place of CURSORS or SELF JOIN for the given example

get free sql tips
agree to terms