Keeping it simple and how to do multiple CTE in a query

Question

I have this simple T-SQL query, it emits a bunch of columns from a table and also joins information from other related tables.

My data model is simple. I have a scheduled event, with participants. I need to know how many participants participate in each event.

My solution to this is to add a CTE that groups scheduled events and counts the number of participants.

This will allow me to join in that information per scheduled event. Keeping the query simple.

I like to keep my queries simple, however, If I ever in the future need to have additonal temporary results accessible during my simple query, what do I do?

I would really like it, if I could have multiple CTEs but I can't, right? What are my options here?

I've ruled out views and doing things at the application data layer. I prefer to isolated my SQL queries.

score 426 · Accepted Answer · edited Mar 31 '16 at 11:57

426

You can have multiple CTEs in one query, as well as reuse a CTE:

WITH    cte1 AS
        (
        SELECT  1 AS id
        ),
        cte2 AS
        (
        SELECT  2 AS id
        )
SELECT  *
FROM    cte1
UNION ALL
SELECT  *
FROM    cte2
UNION ALL
SELECT  *
FROM    cte1

Note, however, that SQL Server may reevaluate the CTE each time it is accessed, so if you are using values like RAND(), NEWID() etc., they may change between the CTE calls.

edited Mar 31 '16 at 11:57

Appulus

18,630
11
38
46

answered Jan 26 '10 at 16:24

Quassnoi

413,100
91
616
614

4

It was that simple. the MSDN documentation was a bit fuzzy around the issue, I couldn't find anything conclusive. Thank you very much! – John Leidegren Jan 26 '10 at 16:27
3

It's documented in [WITH common_table_expression (Transact-SQL)](http://msdn.microsoft.com/en-us/library/ms175972.aspx). You can see this are in the syntax section (take special note of the `[ ,...n ]` in `[ WITH [ ,...n ] ]`. Example C, "Using multiple CTE definitions in a single query," calls this out explicitly. Sadly, this example is not provided in the documentation for SQL 2008 and older (i.e., the example wasn't provided when the OP posted the question). – Brian Jul 10 '14 at 15:01
I get double the amount of records on this :/ – Tom Stickel Apr 08 '19 at 20:18
@TomStickel try only using the half of the query, before the last `UNION ALL` – Quassnoi Apr 09 '19 at 02:03
@Quassnoi Yes that worked. I has done that after writing the comment. Not sure why that 2nd union is even there... – Tom Stickel Apr 09 '19 at 16:19
1

@TomStickel my guess is the 2nd union is there only to illustrate Quassnoi's point that you can reuse a CTE. – Krishna Gupta May 29 '20 at 03:26

score 124 · Answer 2 · edited Apr 16 '20 at 22:14

You certainly are able to have multiple CTEs in a single query expression. You just need to separate them with a comma. Here is an example. In the example below, there are two CTEs. One is named CategoryAndNumberOfProducts and the second is named ProductsOverTenDollars.

WITH CategoryAndNumberOfProducts (CategoryID, CategoryName, NumberOfProducts) AS
(
   SELECT
      CategoryID,
      CategoryName,
      (SELECT COUNT(1) FROM Products p
       WHERE p.CategoryID = c.CategoryID) as NumberOfProducts
   FROM Categories c
),

ProductsOverTenDollars (ProductID, CategoryID, ProductName, UnitPrice) AS
(
   SELECT
      ProductID,
      CategoryID,
      ProductName,
      UnitPrice
   FROM Products p
   WHERE UnitPrice > 10.0
)

SELECT c.CategoryName, c.NumberOfProducts,
      p.ProductName, p.UnitPrice
FROM ProductsOverTenDollars p
   INNER JOIN CategoryAndNumberOfProducts c ON
      p.CategoryID = c.CategoryID
ORDER BY ProductName

@JohnLeidegren: posting a correct answer within 2 minutes of the first correct answer merits an upvote, which I've given, at least. — Peter Majeed, Mar 20 '12 at 15:52
what if one of them is a recursive cte? is there any particular order that needs to be followed? — NAGA, Jan 25 '21 at 02:34

Keeping it simple and how to do multiple CTE in a query

2 Answers2

Linked