This page has 0 threads | Add post

Recursive CTEs

Written by Andy Brown

In this tutorial

Example of when you might need a recursive CTE
Our Example
The Recursive CTE
How does this work?

No blog on CTEs would be complete without mentioning recursive CTEs. These are complicated, and for 90% of SQL programmers unnecessary, so we will consider first when you might need these beasts.

Example of when you might need a recursive CTE

Recursive CTEs are ideal when you store data in a hierarchy. There are two typical scenarios:

When you have a table of staff with primary key StaffId and a separate ManagerStaffId column. You can then use the ManagerStaffId column to recursively create the organisation's hierarchy (Bob works for Sue who works for Alice who works for ... who works for God!).
When you have a menu following the same principle, and you want to use this to build breadcrumbs. A good example of this is Ebay, where you have categories, subcategories, sub-subcategories, etc.

Wherever you have a hierarchical structure stored like this, a recursive CTE is the way to get at your data!

Our Example

We'll use an example of a menu taken from a carnival website:

Each menu contains a ParentMenuId field, giving its immediate parent

Consider the highlighted menu. This is the Senior menu, which belongs in menu number 5 (Fell race), which in turn belongs in menu number 2 (Carnival). This gives a breadcrumb as follows:

Top > Carnival > Fell race > Senior

The Recursive CTE

Here's a query which would show these breadcrumbs for each menu:

WITH menus AS (

-- get the first row (ie the menu whose parent is 0)

SELECT

MenuId,

Caption,

CAST('Top' AS varchar(100)) AS Breadcrumb

FROM

tblMenu

WHERE

ParentMenuId = 0

-- linked to all of those rows whose parent menu id

-- equals this row's menu id

UNION ALL

SELECT

submenus.MenuId,

submenus.Caption,

CAST((m.Breadcrumb + ' > ' + m.Caption) AS varchar(100)) AS Breadcrumb

FROM

tblMenu AS submenus

INNER JOIN menus AS m

ON submenus.ParentMenuId = m.MenuId

)

-- the maximum recursions are set to 4 (this

-- guards against recursive CTEs with bugs in which

-- never finish)

SELECT

MenuId,

Caption,

Breadcrumb + ' < ' + Caption AS Breadcrumbs

FROM

menus

OPTION (MAXRECURSION 4)

Here's the output from this query:

For the Senior menu, for example, you can see the full menu path

How does this work?

To be honest, I struggle to get my head round this - it's one of those queries you can copy and adapt without ever fully understanding. But here goes ...

The first thing we do is to get a record for the top item in the menu:

-- get the first row (ie the menu whose parent is 0)

SELECT

MenuId,

Caption,

CAST('Top' AS varchar(100)) AS Breadcrumb

FROM

tblMenu

WHERE

ParentMenuId = 0

This would produce the following output:

This query shows the single menu which doesn't have a parent

The next thing to do is to show as part of the same set of results (using UNION ALL) the breadcrumbs for the next level down of menus (ie those whose parent menu is number 2):

-- linked to all of those rows whose parent menu id

-- equals this row's menu id

UNION ALL

SELECT

submenus.MenuId,

submenus.Caption,

CAST((m.Breadcrumb + ' > ' + m.Caption) AS varchar(100)) AS Breadcrumb

FROM

tblMenu AS submenus

INNER JOIN menus AS m

ON submenus.ParentMenuId = m.MenuId

After the first iteration, this would give:

After one iteration, we get the top menu plus all of its immediate children.

The clever thing about recursive CTEs is that they will then repeat this process to get all of these children's children, and all of their children, etc.

Previous
tutorial

Subqueries

Previous
part

Update queries using CTEs

Top of page

Next
part

Derived Tables in SQL Server

Next
tutorial

Programming in SQL

You can learn more about this topic on the following Wise Owl courses:

This page has 0 threads

Add a new post

Online tutorials

Tutorials on SQL

CTEs and derived tables

Recursive CTEs

Example of when you might need a recursive CTE

Our Example

The Recursive CTE

How does this work?

Head office

London

Manchester