BLOGS BY TOPIC
BLOGS BY AUTHOR
BLOGS BY YEAR
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.
- CTEs in SQL - Common Table Expressions
- Common Problems with CTEs
- 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:
- 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.
- CTEs in SQL - Common Table Expressions
- Common Problems with CTEs
- Recursive CTEs (this blog)