Aaron Akin

March 18, 2009

Nested Common Table Expressions

Filed under: CTE,SQL,SQL 2005,SQL 2008,TSQL — Aaron Akin @ 8:01 pm

If you’ve looked up a Common Table Expressions in Books Online, you might have noticed that Microsoft says that you cannot have nested CTEs (meaning, you can’t define a CTE in the definition of another CTE. Although this is technically true, you can still accomplish the same functionality in a different manner.

Here is an example of a simple CTE using the AdventureWorks database that will return a list of orders.

USE AdventureWorks

;WITH CTE_Orders (OrderID, OrderDate) AS
(
  SELECT PurchaseOrderID, OrderDate
    FROM Purchasing.PurchaseOrderHeader h
)

SELECT * FROM CTE_Orders
OrderID     OrderDate
----------- ----------
1           2001-05-17
2           2001-05-17
3           2001-05-17
4           2001-05-17
5           2001-05-31
...

What isn’t very clear is the use of multiple CTE definitions in one statement. Here is an example of multiple CTEs used in one statement. Each CTE in the statement has its own definition, and the final SELECT statement is able to query both CTEs. This will return a list of dates that orders were made along with the number of products ordered and the total amount of those products for each date.

;WITH CTE_OrdersDetail (OrderID, OrderDetailQty, OrderDetailAmt) AS
(
  SELECT PurchaseOrderID, OrderQty, LineTotal
    FROM Purchasing.PurchaseOrderDetail
),

CTE_Orders (OrderID, OrderDate) AS
(
  SELECT PurchaseOrderID, OrderDate
    FROM Purchasing.PurchaseOrderHeader h
)

SELECT o.OrderDate, SUM(od.OrderDetailQty) AS TotalOrderQty, SUM(od.OrderDetailAmt) AS TotalOrderAmt
  FROM CTE_Orders o
    INNER JOIN CTE_OrdersDetail od ON o.OrderID = od.OrderID
  GROUP BY o.OrderDate
  ORDER BY o.OrderDate
OrderDate  TotalOrderQty TotalOrderAmt
---------- ------------- -------------
2001-05-17 563           9491.518
2001-05-31 2765          94404.303
2002-01-14 718           39874.7685
2002-01-15 11931         259365.2145
2002-02-08 2768          50781.36
...

If you take this concept of having multiple CTE definitions in one statement, you can then apply it to a statement with nested CTEs. I’m using the following statement to return the same result set as in the previous example, but this time I want to know the number of orders for each date that had more than 100 products ordered.

;WITH CTE_OrdersDetail (OrderID, OrderDetailQty, OrderDetailAmt) AS
(
  SELECT PurchaseOrderID, OrderQty, LineTotal
    FROM Purchasing.PurchaseOrderDetail
),

CTE_Orders (OrderID, OrderDate, IsLargeOrder) AS
(
  SELECT PurchaseOrderID, OrderDate
       , (
         SELECT CASE
                  WHEN SUM(OrderDetailQty) >= 100 THEN 1
                  ELSE 0
                END
           FROM CTE_OrdersDetail d
           WHERE h.PurchaseOrderID = d.OrderID
         )
    FROM Purchasing.PurchaseOrderHeader h
)

SELECT o.OrderDate, SUM(od.OrderDetailQty) AS TotalOrderQty, SUM(od.OrderDetailAmt) AS TotalOrderAmt, SUM(o.IsLargeOrder) AS NumLargeOrders
  FROM CTE_Orders o
    INNER JOIN CTE_OrdersDetail od ON o.OrderID = od.OrderID
  GROUP BY o.OrderDate
  ORDER BY o.OrderDate
OrderDate  TotalOrderQty TotalOrderAmt         NumLargeOrders
---------- ------------- --------------------- --------------
2001-05-17 563           9491.518              1
2001-05-31 2765          94404.303             5
2002-01-14 718           39874.7685            1
2002-01-15 11931         259365.2145           26
2002-02-08 2768          50781.36              5
...

As you can see from this example, the CTE_OrdersDetail expression is being referenced within the definition of the CTE_Orders expression, giving you the functionality of nested CTEs. In order for a statement like this to work properly, you must declare each CTE definition before it is used in another CTE. This means that since CTE_OrdersDetail is being reference within CTE_Orders, I must declare CTE_OrdersDetail first. Otherwise, if the two definitions were switched, SQL would not be able to find the CTE_OrdersDetail object when it goes to verify the CTE_Orders expression.

Advertisements

12 Comments »

  1. that was great. so clear.. you r my saver

    Comment by great article — July 18, 2009 @ 1:59 pm | Reply

  2. Hi Aaron,
    Its nice,
    I have one scenario where i have three tables tb1=100 records
    , tb2=50
    , tb3=20
    select tb1.name,tb2.visit, tb3.feedback
    tb1 left outer join tb2 on tb1.id=tb2.id left outer join tb3 tb1.id=tb3.id where tb2.visit =’last’ and tb3.Feedback_visit = ‘last’
    and tb1.revenue date =’10/10/2009′

    I need to display the 100 records for the revenue date ’10/10/2009′ where as the duplications in table2 and table3 can be null

    But when i run the query with the condition

    tb2.visit =’last’ and tb3.Feedback_visit = ‘last’

    im getting only the 10 rows which is the least
    i need to display the complete 100 records of the revenue day though it is not having visit and feedback for the product.

    Comment by Mohammed — October 17, 2009 @ 8:20 am | Reply

    • Hi Mohammed,

      You are very close. Instead of putting “tb2.visit =’last’ and tb3.Feedback_visit = ‘last’” in your WHERE clause, you need to put these into the join statements. By putting them into the WHERE clause, you are turning your LEFT OUTER JOINs into INNER JOINs, which is why you only get 10 records. Try the query below.

      select tb1.name,tb2.visit, tb3.feedback
      from tb1
      left outer join tb2 on tb1.id = tb2.id and tb2.visit = ‘last’
      left outer join tb3 on tb1.id = tb3.id and tb3.feedback_visit = ‘last’
      where tb1.revenue_date = ’10/10/2009′

      Comment by Aaron Akin — October 17, 2009 @ 8:56 am | Reply

  3. Great article…helped me a lot!!!

    I was able to use two CTE’s in my dynamic sql and call it from my stored procedure after reading your article.

    THANK YOU!!!!

    Comment by Anonymous — May 5, 2010 @ 11:25 am | Reply

  4. Very nice article! simple and to the point!

    Comment by Deepak Aggarwal — May 28, 2010 @ 6:22 am | Reply

  5. Thanks Aaron – great article and helped me create the most awesome sproc ever using “nested” CTEs. Thank you for taking the time to write this and share!

    Comment by sue — January 5, 2011 @ 12:21 pm | Reply

  6. Very helpful just when I needed it. Thank you and get them MicroSoft SS designers told that we need nested CTE’s!!!

    Comment by Derek — July 9, 2011 @ 6:31 am | Reply

  7. Thanks Sir, nice article……….

    Comment by upendra2009ktl@gmail.com — August 20, 2012 @ 4:26 am | Reply

  8. I did this accidentally, and was scratching my head wondering why it worked! Thanks for a clear example, this is very helpful.

    Comment by Nadine — December 7, 2012 @ 7:36 pm | Reply

  9. You write the way I like to learn: step by step, with great examples. Thank you so much for this article; I will be looking for more with your name on them.
    -Irene

    Comment by Anonymous — June 19, 2013 @ 11:51 am | Reply

  10. WITH
    Source ———1———
    As
    (
    SELECT * FROM emp
    ),
    destination—-2———-
    AS
    (
    SELECT *
    FROM Source
    WHERE E_id = 4
    )
    SELECT * FROM destination

    Comment by Sangu — October 24, 2014 @ 2:54 am | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: