Nested CTEs

Day 11 of Common Table Expression Month (June) at SteveStedman.com, today we will be taking a look at how to use multiple nested CTEs in a query.

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.

The same concept applies for all of the nested CTEs in a nested CTE query. Any CTE query can access the CTEs declared prior to that one, but not the ones after.

Nested CTEs

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. It also 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:

1. The outer SELECT statement is accessing OrderItemsPerCustomerCTE and CustomersOrdersWithNamesCTE.
2. CustomersOrdersWithNamesCTE references a table and it also references CustomersWithMostOrdersCTE which is considered nested.
3. CustomersWithMostOrdersCTE doesn’t reference any CTEs, it only references tables.
4. OrderItemsPerCustomerCTE doesn’t reference any CTEs, it also only references tables.

In this result we have ended up with 3 CTEs, two nested, and one not nested. If the need arises, we can have nested CTEs that are many levels deep.

Nesting CTEs can be a great way to accomplish queries that would otherwise have been difficult to create.

---

Related Links:

• My Book on Common Table Expressions (free if you sign up for my mailing list)
  
• cte_demo sample database

---

---

 

---

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!