Recursive query with SQL Server’s Common Table Expressions

In my on-going efforts to expand my horizons in the land of SQL Server, I’m going to discuss recursive queries using the SQL Server feature called Common Table Expressions or CTEs.  First off, a CTE is essentially a temporary result set that is available only in the scope of the query that creates it.

Here’s a quick example:

Alright, I’m fully aware that this is a lame example, but I will get to something a little more interesting in a moment.  I’m not going to go into full detail on the whole CTE thing, but you can find more info on the web.  The real goal here is to flex the power of the CTE to do recursive queries.  Why would I need to do a recursive query, you ask?  Well, you might have to be a little crazy, but there are some situations that recursion fits the bill.  Two words, nested categories.

Let’s say that you have a table with a CategoryId and an optional field, ParentId.  ParentId, when it is not null, it’s value will refer to the CategoryId of its parent.  How about some example data to work with?

CategoryId CategoryName ParentId
1 Car NULL
3 Corvette 1
4 Porsche 1
5 Tahoe 2

There’s just enough data to get the point across.  You’ll notice that Car and SUV have no ParentId, which makes them root nodes.  You’ll also notice that Corvette, Porsche and Tahoe all have values for their ParentId, which makes them the child nodes.  Given this data, let’s assume that you want to get all Cars.  With our data, this would be pretty simple, but humor me and pretend there is more nesting going on with more data.  Let’s take a look at what we could do to accomplish this.

Since we’ve already discussed what the whole WITH thing is (CTE), so lets get into what is going on under the hood.  There are two selects going on which get unioned together (merged).  The first select is pretty straight-forward, just grabbing the record who’s CategoryName is ‘Car’.  Then, the next step is to get all the descendants of the ‘Car’ category.  That’s where things get recursive and interesting.

Check this out:

This is pretty straight-forward also, just grabbing the info we want from the Category table, incrementing the Level.  The INNER JOIN part is where the recursion comes in:

Notice that Category is being joined to the very CTE that the query is building.  To get the proper hierarchy, the CategoryId of TempCategories is bound to the ParentId of Category.  The data that will be returned here is the entire tree beneath the ‘Car’ node.

There we have it!  I hope this helps at least a little!