How to use SQL Server CTEs to make your T-SQL code readable by humans


By:   |   Updated: 2013-06-17   |   Comments (11)   |   Related: More > Scripts

Problem

If you're the sort of person who can effortlessly write complicated queries in SQL using a single SELECT statement, please click away from this article now!  However, if ( like me ) your brain spins in proportion to the complexity of the query you're writing, read on to see how to use CTEs (Common Table Expressions) to break up complex queries into several distinct steps.

Solution

Let's start with a sample library database. I've deliberately kept this as simple as possible. It consists of just two tables:

Authors and their books

Each author can have one or more books in the tblBook table, with the AuthorId column being used as a foreign key. If you want to reproduce this yourself, run this script:

-- create a database to hold OUR books
CREATE DATABASE Library
GO
USE Library
GO
-- create a table of authors
CREATE TABLE tblAuthor(
 AuthorId int PRIMARY KEY,
 FirstName varchar(50),
 LastName varchar(50)
)
GO
-- add some authors
INSERT tblAuthor (AuthorId, FirstName, LastName) VALUES (1, 'John', 'Wyndham')
INSERT tblAuthor (AuthorId, FirstName, LastName) VALUES (2, 'Barbara', 'Kingsolver')
INSERT tblAuthor (AuthorId, FirstName, LastName) VALUES (3, 'Jane', 'Austen')
-- create a table of books
CREATE TABLE tblBook(
 BookId int PRIMARY KEY,
 BookName varchar(100),
 AuthorId int,
 Rating int
)
-- add some books
INSERT tblBook (BookId, BookName, AuthorId, Rating) VALUES (1, 'The Day of the Triffids', 1, 10)
INSERT tblBook (BookId, BookName, AuthorId, Rating) VALUES (2, 'The Chrysalids', 1, 8)
INSERT tblBook (BookId, BookName, AuthorId, Rating) VALUES (3, 'The Lacuna', 2, 10)
INSERT tblBook (BookId, BookName, AuthorId, Rating) VALUES (4, 'The Poisonwood Bible', 2, 8)
INSERT tblBook (BookId, BookName, AuthorId, Rating) VALUES (5, 'Pride and Prejudice', 3, 9)

You should now have 3 authors and 5 books. 

A single-query solution

Suppose that you want to list out in alphabetical order the authors who have written more than 1 book. You could do this with a single query:

-- list authors who have written more than one book
SELECT
 a.FirstName + ' ' + a.LastName AS Author,
 COUNT(*) AS 'Number of books'
FROM
 tblAuthor AS a
 INNER JOIN tblBook AS b ON a.AuthorId=b.AuthorId
GROUP BY
 a.FirstName + ' ' + a.LastName
HAVING
 COUNT(*) > 1
ORDER BY
 Author

There's nothing wrong with this approach. Indeed for relatively simple queries like this one it's probably the best one, but as your queries get more complicated, it'll become ever harder to keep the whole picture in your head.  The reason I like CTEs so much is that they allow you to break down a problem into different parts (always the holy grail in computing). To see how this works, we'll solve the above problem again - but more slowly.

A CTE solution

The following solution uses a common table expression, giving the following advantages:

  • It avoids the need to repeat expressions in HAVING or GROUP BY clauses;
  • It reads more logically and more intuitively. 

To get things started, we need to get a list showing each author's id, together with the number of books they've written:

USE Library;
-- list authors with the number of books they've written
WITH cteBooksByAuthor AS (
SELECT AuthorId, COUNT(*) AS CountBooks
FROM tblBook
GROUP BY AuthorId
)
-- use this CTE
SELECT * FROM BooksByAuthor

Notice that the statement immediately before the CTE has to end with a semi-colon where I have "USE Library;", but this would be for any statement that is prior to the CTE.

What this query does is to create a temporary table-like object called cteBooksByAuthor, then immediately refers to it in the following statement. Note that CTEs are like cheese souffles: they can only be used as soon as they've been created.  If we issued another query after this the CTE can no longer be referenced.

Now that you've created a CTE, we can join it - as for any other table - to another table to get at the authors' names.  So our final query is:

USE Library;
-- list authors with the number of books they've written
WITH cteBooksByAuthor AS (
 SELECT AuthorId, COUNT(*) AS CountBooks
 FROM tblBook
 GROUP BY AuthorId
)
-- use this CTE to show authors who have written
-- more than 1 book
SELECT 
 a.FirstName + ' ' + a.LastName AS Author,
 cte.CountBooks AS 'Number of books'
FROM
 cteBooksByAuthor AS cte
 INNER JOIN tblAuthor AS a ON cte.AuthorId=a.AuthorId
WHERE cte.CountBooks > 1

Not convinced?  A slightly more complicated example ...

If the above doesn't convince you, consider this slightly more complicated query. Suppose that you want to show for each author their book with the highest rating. You could easily accomplish this with a single correlated subquery, but many SQL programmers will find it easy to break the task into two parts.  First get a list for each author of their highest rating:

Highest rating for each author

You can now link this to the books table, to get the books shown highlighted below:

Highest rated books

Here's our CTE to return the right results:

USE Library
GO
-- get a "temporary table" (a CTE) of highest score for each author
-- (don't need a semi-colon as this is now first statement in batch)
WITH HighestRatings AS (
 SELECT 
  author.AuthorId,
  MAX(book.Rating) AS HighestRating
 FROM
  tblBook AS book
  INNER JOIN tblAuthor AS author ON book.AuthorId=author.AuthorId
 GROUP BY 
  author.AuthorId
)
-- get the name of book and name of author 
SELECT
 author.FirstName + ' ' + author.LastName AS Author,
 book.BookName AS Title,
 hr.HighestRating AS Rating
FROM
 HighestRatings AS hr
 INNER JOIN tblBook AS book ON hr.HighestRating=book.Rating and hr.AuthorId=book.AuthorId
 INNER JOIN tblAuthor AS author ON book.AuthorId=author.AuthorId

This would return the following set of rows:

The final answer from CTE

Whether you think that the CTE solution is easier to write is a matter for debate; but conceptually it is easier to read, I'd claim!

Next Steps

CTEs can do more than is shown above.  You could try looking at one of these tips:

  • Writing recursive CTEs (not for the faint-hearted ...)
  • Consideration the relative speed of CTEs against other SQL methods of accessing data


Last Updated: 2013-06-17


get scripts

next tip button



About the author
MSSQLTips author Andy Brown Andy Brown is a director of Wise Owl, a UK training company, and creator of many SQL Server blogs, tutorials and videos.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Monday, April 14, 2014 - 10:29:52 PM - Mitchell Back To Top

Good work!

 

Now i start to use CTEs in my stored procedures usually, and it seems to be more intuitively and easier to read again.

 

thks


Tuesday, November 12, 2013 - 11:31:18 AM - John Back To Top

I like using sub queries instead of CTEs, like:

 

Select some cols

from

  (Select some cols

    from SomeTable

  ) a

join

  (Select some cols

    from SomeTable

  ) b on a.col = b.col

 

I suspect that is similar to CTEs, and doesn't have a performance hit. Is that correct? Do you see any drawbacks to the sub query approach?

 

 


Wednesday, September 11, 2013 - 9:48:03 AM - Dom Back To Top

Good article, but wouldn't a VIEW be a better approach?  You can reuse a view; you can't reuse a CTE.  Of course, I never knew how either perform during optimization


Monday, September 09, 2013 - 8:27:19 AM - shmulik Back To Top

how does CTE affect performance?


Friday, August 23, 2013 - 12:36:11 PM - Andy Brown Back To Top

ElliotH: you are so right!  How on earth that happened I don't know.  Thanks for highlighting this.


Friday, August 23, 2013 - 8:59:27 AM - ElliotH Back To Top

Usefull but always good to make sure your scripts work... :-(

-- use this CTE

SELECT * FROM BooksByAuthor

 

from the 1st CTE example should be ..

-- use this CTE

SELECT * FROM cteBooksByAuthor


Tuesday, August 20, 2013 - 5:31:01 PM - Nicholas Zagaroli Back To Top

I too have been using CTEs for a few years now and have not noticed any performance degradation.  In most cases I think you get an improvement; but I think that’s attributed to a better structured query having thought it out a little more than when I jam everything into a single query.

 

Couple of things I have experienced:

 

If the CTE tables are large it can have an adverse effect on performance.

 

I have been able to replace quite a few procedures which were using cursors with views using CTE table, now that is a performance improvement.

 

Good article, keep up the good work.

 

Thank you,

 


Thursday, July 18, 2013 - 12:01:21 PM - Mark Hoyt Back To Top

Good article!

I've used CTE's for the past few years and have not noticed any performance degradations due to their use.

The only issue I've had is that, as noted in the article, the CTE can only be used as soon as it is created. So you can't use it in successive select or updates.

Thanks for the article!


Thursday, June 20, 2013 - 10:11:43 AM - Andy Brown Back To Top

Hi Rafferty - to be honest, I have no idea about the performance.  However, since SSMS optimises all queries, I wouldn't have thought it would be any slower.  Anyone else out there to contribute?


Wednesday, June 19, 2013 - 10:18:45 PM - Rafferty Back To Top

Thanks for the tip.

How about the performance? Is the CTE approach faster, slower or the same?


Tuesday, June 18, 2013 - 11:32:57 AM - Sandra Back To Top

Excellent solution! Thnx



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools