These queries will be using that database that was set up in a previous posting on the CTE_DEMO Sample Database, if you haven’t set up the sample database, download it and set it up now.
Russian Nesting Dolls
If you have ever seen the Matryoshka dolls known as the Russian nesting dolls or babushka dolls they are very interesting. They start with a large wooden doll that when opened contains a slightly smaller doll, then inside of that one an even smaller doll. These dolls often have five to eight dolls nested inside of each other.
The interesting thing about these dolls is that they have one specific way that they can all fit together; the smallest doll must be the first one put inside of the next smallest. If you were to lose one of the medium dolls, they could still be placed together; there would just be some extra space inside.
Nested CTEs often remind me of the Russian nesting dolls. Think of the first CTE in a multiple CTE query as the smallest doll. That first CTE can’t reference any other CTEs, much like the smallest doll can’t fit any other dolls inside of it.
Think of the second CTE in a multiple CTE query as the second smallest doll in the Russian dolls. It can only have one doll fit inside of it. CTEs are similar in that the only CTE that can be referenced by the second CTE in a multiple CTE query is the first CTE.
The third CTE is like the third smallest Russian doll. The third smallest doll can only have the smallest or second smallest doll placed inside of it. With the third CTE it can only access the first or the second CTE declared, and not any of the CTEs declared later.
Take an example of a multiple CTE query with 4 nested CTEs as follows with the … being replaced with a T-SQL query.
;WITH CTE1 AS ( .... ), CTE2 AS ( .... ), CTE3 AS ( .... ), CTE4 AS ( .... ) SELECT * FROM CTE4;
Nested CTEs Sample Code
;WITH CustomersWithMostOrdersCTE AS -- First CTE ( SELECT TOP 10 CustomerID, count(1) as numOrders FROM SalesInvoice GROUP BY CustomerID ORDER BY count(1) DESC ), -- Second CTE CustomersOrdersWithNamesCTE AS ( SELECT cust.FirstName, cust.LastName, cte.* FROM Customer AS cust INNER JOIN CustomersWithMostOrdersCTE AS cte ON cte.CustomerID = cust.CustomerID ) SELECT * FROM CustomersOrdersWithNamesCTE;
At this point the CTEs would be considered nested since one CTE references the other.
Now we will add another CTE to get the total number of items per invoice. This CTE will not be nested, as it will only be accessed from the outside query, and not from inside of any CTE, and it is does not reference any of the other CTEs.
;WITH CustomersWithMostOrdersCTE AS -- First CTE ( SELECT TOP 10 CustomerID, count(1) as numOrders FROM SalesInvoice GROUP BY CustomerID ORDER BY count(1) desc ), -- Second CTE CustomersOrdersWithNamesCTE AS ( SELECT cust.FirstName, cust.LastName, cte.* FROM Customer AS cust INNER JOIN CustomersWithMostOrdersCTE AS cte ON cte.CustomerID = cust.CustomerID ), -- Third CTE OrderItemsPerCustomerCTE AS ( SELECT invoice.CustomerID, count(1) as numItems FROM SalesInvoice as invoice INNER JOIN SalesInvoiceDetail as detail ON detail.invoiceID = invoice.invoiceid GROUP BY invoice.CustomerID ) SELECT FirstName, LastName, NumOrders, NumItems FROM OrderItemsPerCustomerCTE AS items INNER JOIN CustomersOrdersWithNamesCTE AS names ON items.CustomerID = names.CustomerID;
To walk through what is happening with the example shown in Figure 6.8 follow these steps:
- The outer SELECT statement is accessing OrderItemsPerCustomerCTE and CustomersOrdersWithNamesCTE.
- CustomersOrdersWithNamesCTE references a table and it also references CustomersWithMostOrdersCTE which is considered nested.
- CustomersWithMostOrdersCTE doesn’t reference any CTEs, it only references tables.
- OrderItemsPerCustomerCTE doesn’t reference any CTEs, it also only references tables.
Nesting CTEs can be a great way to accomplish queries that would otherwise have been difficult to create.
Nested CTEs in the Common Table Expressions Book
If you enjoyed this posting, and want to learn more about common table expressions, please take a look at my book on CTE’s at Amazon.com. The book is titled Common Table Expressions – Joes 2 Pros® – A CTE Tutorial on Performance, Stored Procedures, Recursion, Nesting and the use of Multiple CTEs.
Sorry .. The book is out of print now. I am working on an updated revision.