Common Table Expressions (CTEs) - SQL Training
Part three of a three-part series of blogs

This blog gives training on writing CTEs in SQL, or Common Table Expressions to give them their full title. The blog also includes a section on creating recursive CTEs.

  1. CTEs in SQL - Common Table Expressions
  2. Common Problems with CTEs
  3. Recursive CTEs (this blog)

You can find many more articles like this on our SQL tutorial page.  To get a rounded view of how to program in SQL, have a look at our introductory and advanced SQL training.

Posted by Andy Brown on 20 October 2011

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Recursive CTEs

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:

  1. 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!).
  2. 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:

Carnival menu table

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:

Output from CTE of menus

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:

Output from first line of CTE

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:

Results of first iteration

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.

  1. CTEs in SQL - Common Table Expressions
  2. Common Problems with CTEs
  3. Recursive CTEs (this blog)
This blog has 0 threads Add post