Using multiple CTEs in one query

This is a fairly simple thing to do, but I had to look it up the other day and thought it would make a good blog. This is a good example of a blog that everyone should write to show they know a particular skill.

TL;DR: Use a comma between CTE definitions.

Let’s imagine that you need to combine a few queries together and the code is complex. You decide to use a CTE to make things easier. Suppose I have this query:

SELECT p.title , p.firstname , p.lastname FROM Person.BusinessEntity be INNER JOIN Person.Person p ON p.BusinessEntityID = be.BusinessEntityID WHERE ( SELECT COUNT(*) FROM Person.BusinessEntityAddress bea WHERE bea.BusinessEntityID = be.BusinessEntityID ) > 1 AND ( SELECT COUNT(*) FROM Person.PersonPhone pp WHERE pp.BusinessEntityID = be.BusinessEntityID ) > 1

That’s ugly, and I want to move the subqueries. I know this is slightly contrived, but imagine that what you really want is a couple views like:

CREATE VIEW BEACount AS SELECT bea.BusinessEntityID , cnt = COUNT(*) FROM Person.BusinessEntityAddress bea WHERE bea.BusinessEntityID = be.BusinessEntityID CREATE VIEW PersonPhoneCount AS SELECT bea.BusinessEntityID , cnt = COUNT(*) FROM Person.PersonPhone pp WHERE pp.BusinessEntityID = be.BusinessEntityID

However you don’t want to create views, what you really want is to make both of these queries CTEs and then call them from the original query.

I wasn’t sure how to do this, so I Googled. The first result was my friend, Pinal Dave’s blog. It took me about 10 seconds to read his explanation and apply it to my issue.

WITH BEACountCTE AS ( SELECT bea.BusinessEntityID , cnt = COUNT(*) FROM Person.BusinessEntityAddress bea GROUP BY bea.BusinessEntityID ) , PersonPhoneCountCTE AS ( SELECT pp.BusinessEntityID , cnt = COUNT(*) FROM Person.PersonPhone pp GROUP BY pp.BusinessEntityID ) SELECT p.title , p.firstname , p.lastname FROM Person.BusinessEntity be INNER JOIN Person.Person p ON p.BusinessEntityID = be.BusinessEntityID INNER JOIN PersonPhoneCountCTE pp ON pp.BusinessEntityID = p.BusinessEntityID INNER JOIN BEACountCTE bea ON bea.BusinessEntityID = p.BusinessEntityID WHERE BEA.cnt > 1 AND PP.cnt > 1

Note that at the top I have one WITH statement and both of my CTEs are separated by commas.

Easy enough, a quick thing to look up, and a handy item to know. If you are breaking up queries and using CTEs to make things easier to read, use a comma between your CTEs.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , . Bookmark the permalink.

1 Response to Using multiple CTEs in one query

  1. I posted on this awhile back and was surprised at how few people knew it could be done. Of course it doesn’t show up as an example until BOL 2012 and isn’t even shown as an example until 2008 R2. Interestingly the DB2 people I’ve talked to have known about it for years.

    Like

Comments are closed.