My Adventures in Coding

May 2, 2014

SQL Server – Simple Recursive Query Example

Filed under: SQL,SQL Server — Brian @ 2:50 pm
Tags: , , , ,

Every once in a while I need to write a recursive query in SQL Server and I always forget the syntax so I have to track down a simple example to help me remember. However, if you are trying to write a recursive query for the first time, I find some of the examples online to be a little bit too complicated. So I wanted to post an example, but also give you the script to create the table and populate it with data so you can see how it works and try it yourself. In this post I use the common example of a table with countries, states, and cities and where we want to get a list of all cities in a single country. Enjoy!

Create a table called “Area”:

   AreaID int NOT NULL,
   AreaName varchar(100) NOT NULL,
   ParentAreaID int NULL,
   AreaType varchar(20) NOT NULL
( AreaID ASC

Add some “Area” data:

INSERT INTO dbo.Area(AreaID,AreaName,ParentAreaID,AreaType)
VALUES(1, 'Canada', null, 'Country')

INSERT INTO dbo.Area(AreaID,AreaName,ParentAreaID,AreaType)
VALUES(2, 'United States', null, 'Country')

INSERT INTO dbo.Area(AreaID,AreaName,ParentAreaID,AreaType)
VALUES(3, 'Saskatchewan', 1, 'State')

INSERT INTO dbo.Area(AreaID,AreaName,ParentAreaID,AreaType)
VALUES(4, 'Saskatoon', 3, 'City')

INSERT INTO dbo.Area(AreaID,AreaName,ParentAreaID,AreaType)
VALUES(5, 'Florida', 2, 'State')

INSERT INTO dbo.Area(AreaID,AreaName,ParentAreaID,AreaType)
VALUES(6, 'Miami', 5, 'City')

If I do a select by AreaType “City”:

select * from dbo.Area
where AreaType = 'City'

I get both Saskatoon and Miami:

AreaID	AreaName	ParentAreaID	AreaType
4       Saskatoon       3            City
6       Miami           5            City

However, what if I wanted to return all cities in Canada?

You can accomplish this by doing a recursive select which uses a common table expression (CTE).

--anchor select, start with the country of Canada, which will be the root element for our search
SELECT AreaID, AreaName, ParentAreaID, AreaType
FROM dbo.Area 
WHERE AreaName = 'Canada'
--recursive select, recursive until you reach a leaf (an Area which is not a parent of any other area)
SELECT a.AreaID, a.AreaName, a.ParentAreaID, a.AreaType 
FROM dbo.Area a 
INNER JOIN AreasCTE s ON a.ParentAreaID = s.AreaID 
--Now, you will have all Areas in Canada, so now let's filter by the AreaType "City"
where AreaType = 'City' 

Now we get back the following results for cities in Canada:

AreaID	AreaName	ParentAreaID	AreaType
4       Saskatoon       3               City

That’s it! Now we have written a simple recursive query!



  1. You can use self Join statements also to achieve the result. However CTE and Self-Join both are usable.

    Syntax is:

    select a.AreaId, a.AreaName, a.AreaType, a.ParentId from area a inner join area s on s.AreaId=a.ParentId
    where s.ParentId=(select AreaId from area where AreaName=’Canada’)

    Comment by J R Reddy — January 27, 2015 @ 8:50 am | Reply

    • But this isn’t recursive is it?

      Comment by Poe — June 23, 2015 @ 4:00 am | Reply

      • Right, that self join example suggested in the previous comment assumes a fixed depth, while the CTE query does not.

        Comment by Brian — March 6, 2017 @ 8:10 pm

  2. very nice post

    Comment by João — May 21, 2015 @ 5:40 am | Reply

  3. como puedo crear un script que genere recursividad de manera automat

    Comment by Jorge Alvarez — August 4, 2015 @ 8:56 am | Reply

  4. This really helped me understand recursive queries. I was struggling with the other, complex examples on the web.Thanks!

    Comment by richwking — September 6, 2015 @ 8:32 pm | Reply

  5. I really like this simple example, makes a lot of sense.

    Comment by Brian — July 16, 2016 @ 8:03 am | Reply

  6. Thank you!! Easy and well Explained!!. I have a doubt, I’ve been trying to do the same but instead of CTE table I was trying a table variable, but I don’t get the same results. Do recursive queries only work with CTE?. Thanks in advanced

    Comment by Jonathan — February 9, 2017 @ 6:34 pm | Reply

    • Hi, could you post an example of the query you were building using a table variable?

      Comment by Brian — March 7, 2017 @ 11:02 am | Reply

  7. fiinally grabbing this concept, thanks

    Comment by trz — April 4, 2017 @ 6:22 am | Reply

  8. Thanks, it took about 10 minutes to convert field names and return the stuff I wanted. I have a large dataset where I will let the user plug in an additional parameter
    i.e. WHERE ParentID IS NULL and TransId=myUsersVariable
    to grab a specific parent.
    Again, MUCH appreciated!!!! 🙂

    Comment by joSQLjo — May 10, 2017 @ 1:00 pm | Reply

  9. Very simple article which articulates the concept effectively! Thanks a lot!

    Comment by Vidyasagar Jyoshi — July 4, 2017 @ 1:18 pm | Reply

  10. Thank you , That’s solved my problem

    Comment by Anas Tuffaha — July 27, 2017 @ 1:23 am | Reply

  11. AreaID , AreaName, ParentAreaID as State, AreaType
    this Query Shows State Id I want Show State Name insted of ParentAreaID

    Comment by Faisal — July 31, 2017 @ 12:47 am | Reply

  12. This is awesome. Have been trying to get my head around recursive queries and the example is great, really useful.

    Comment by MarkD — August 7, 2017 @ 1:50 am | Reply

  13. Added example as SQL fiddle!6/5f2939/3/0

    Comment by cameronstewart — January 16, 2018 @ 8:47 pm | Reply

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Blog at

%d bloggers like this: