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”:

CREATE TABLE dbo.Area(
   AreaID int NOT NULL,
   AreaName varchar(100) NOT NULL,
   ParentAreaID int NULL,
   AreaType varchar(20) NOT NULL
CONSTRAINT PK_Area PRIMARY KEY CLUSTERED 
( AreaID ASC
) ON [PRIMARY])
GO

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).

WITH AreasCTE AS 
( 
--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'
UNION ALL 
--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"
SELECT * FROM AreasCTE  
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!

Advertisements

10 Comments »

  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. 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


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: