This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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

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.

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

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.

Parts of this blog
CTEs in SQL - Common Table Expressions Common Problems with CTEs Recursive CTEs (this blog)

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Recursive CTEs

Example of when you might need a recursive CTE

Our Example

The Recursive CTE

How does this work?

Head office

London

Manchester