Common Table Expressions (CTE’s) are a very useful tool available to us when querying databases in Microsoft SQL Server. CTE’s can help you gather the information you want quickly without needing to write much code, or persist an object in the database.
Common Table Expressions are just one type of table expression we have available to us in SQL Server. For an introduction to all the different kinds of table expressions, take a look at the full beginner-friendly tutorial:
SQL Server Table Expressions: The Ultimate Guide for Beginners
In this tutorial, we will answer the following questions about CTE’s:
- What is a Common Table Expression?
- What is the syntax for creating a Common Table Expression?
- How can Common Table Expressions help us?
- What are the limitations of a Common Table Expression?
- Tips and tricks
Everything discussed in this tutorial can also be found in the following FREE Ebook:
FREE Ebook on SQL Server Table Expressions!
This Ebook provides an excellent summary of everything you need to know about the different table expressions we can create in Microsoft SQL Server, including Common Table Expressions. It will be a great resource for you to reference throughout your career as a data professional. Make sure you download it today!
Let’s dive in.
1. What is a Common Table Expression?
A Common Table Expression (better known as a CTE) is a temporary table expression that is defined directly above an outer query. The CTE contains an inner query, and is given an alias. That alias is referenced in the FROM clause of the outer query.
A CTE is not persisted in the database as an object. They are similar to derived tables in that way. As soon as the outer query completes, the CTE falls out of scope and cannot be used again.
This is different from a View or an Inline Table Valued function where the table expression is created as a database object, and can be used repeatedly.
2. What is the syntax of a Common Table Expression?
It’s a little strange:
;WITH <CTE alias name> AS ( <inner query> ) SELECT <column list> FROM <CTE alias name>
Let’s break this down, starting from the top.
No, the semicolon in front of the word ‘WITH‘ is not a typo.
The word ‘WITH‘ is actually a keyword in SQL Server. In the definition of the CTE, if we tried to use the word WITH by itself, SQL Server would get confused and give us an error message. Therefore, we need to put a semicolon in front of it, which officially terminates any statements that exist in your query window before the CTE.
This is just one of those things you will need to remember about CTE’s: When you create a CTE, always precede the word ‘WITH‘ with a semicolon.
(The only way you could get away with not putting a semicolon in front of the word WITH is when the CTE is the first query in your query window. But just do yourself a favor and forget about that exception)
Moving on.
The Name of your CTE can be anything you want it to be. You’ll want to be a good programmer and name it something meaningful, though. Don’t just make the name a single letter, like ‘C‘. That’s bad practice.
The inner query of your CTE is defined within parentheses. This is the query whose result set will serve as the actual table expression, represented by the name of your CTE.
Finally, we have the outer query. This must be directly below the ending parentheses of your inner query. This outer query will reference the name of the CTE in the FROM clause (just like how a normal table is referenced).
But the best way to understand this is to actually try it out! Let’s look at some examples in the next section.
3. How can Common Table Expressions help us?
Here is our first simple example of a a CTE:
The inner query of our CTE simply pulls the Customer ID and Email address for Customers that have an email address. We give that result set a name, CustomersWithEmail.
Then, in the outer query, we query that table expression name just like we would any other table: In the FROM clause.
This is one of the reasons I prefer to use CTE’s over derived tables. I think the syntax of the outer query reads a bit easier since it is the same as it would be for any other table.
But that example doesn’t really show us the full benefit of using a CTE. Take a look at this CTE:
The inner query hasn’t changed, but our outer query has.
We used the results of our CTE in a JOIN with the Customers table to get the First Name and Last Name of our Customers with an Email address.
See how clean that outer query is?
If we wanted to get the same results through using a derived table instead, we would need to do this:
Not as clean, in my opinion.
But cleanliness isn’t the only thing we need to worry about. Sometimes a CTE just makes things easier.
Take this example:
Let’s talk about what this code does. In the inner query, we query the Orders table that has an OrderDate column. We use the DATENAME system function to get the actual name of the month for each order. For example, an order made on 9/21/2020 will have a month name of ‘September‘.
We also filter our results in the inner query to show orders placed in 2020 only.
So when that inner query completes, we’ll see the month name of all our orders placed in 2020. We can actually highlight that inner query and run it by itself to show you the results:
Cool. The next thing to look at is the the work done in our outer query. The outer query simply counts the number of orders placed in each month for 2020 and returns the results. Here are those results again:
The outer query is clean again, which is good, but think about what we would need to do if we didn’t use a CTE.
In our outer query, we use the ‘OrderMonth‘ column twice: Once in the SELECT list, and another time in the GROUP BY clause.
But remember, it took some effort in the inner query to figure out the OrderMonth value.
So If we didn’t use a CTE, we would have needed to repeat that effort to get the same results:
There is a golden rule you need to remember when coding: Don’t repeat yourself!
4. What are the limitations of a Common Table Expression?
We need to discuss some of the rules about the inner query of a CTE. These aren’t really limitations. They are simply rules you need to follow:
- In your inner SELECT statement, your columns must all have names. If one of your columns is the result of a calculation or an aggregation, you need to make sure you give that column an alias.
- Also when it comes to columns, your column names must be unique. You can’t have two or more columns in your inner SELECT statement with the same name.
- You can’t have an ORDER BY clause in your inner SELECT statement. The only exception to this rule is if there was also TOP or OFFSET-FETCH used in your SELECT list.
The only actual limitation is that the life of your CTE ends when the outer query falls out of scope.
There is no persisting object created in the database. Sometimes that’s exactly what you want, but sometimes it isn’t.
For example, this doesn’t work:
It doesn’t matter that the second SELECT statement was ran in the same batch. The CTE ended with the completion of the outer query, which was SELECT * FROM CustomersWithEmail. We cannot use the CTE again after that!
5. Tips and tricks
Here is a list of some helpful tips and tricks you should know when working with CTE’s:
Tip # 1: Your column aliases can be external
Let’s look at this example of a CTE again:
Notice our alias ‘OrderMonth‘. This is an Internal alias, meaning it is directly next to the derived column in the SELECT list.
But here is how you would set it up if you wanted the alias to be external:
You put the names of your columns in parentheses next to the alias of the CTE.
Notice we need to outline all the columns, and not just the one that is the result of our DATENAME function.
By the way, if you put aliases both externally and internally (because you like to party), the external aliases will take precedence.
Tip # 2: You can define multiple CTE’s at once
You would separate them by a comma. Here is an example:
First, we have a CTE with the alias ProductInfo. After the closing parentheses of that CTE, we have a comma, then another CTE named OrderInfo.
After the closing parentheses for that second CTE, we have our outer query.
There are two things to be aware of when you have multiple CTE’s:
- A CTE can refer to any CTE that came before it. For instance, our OrderInfo CTE refers to the first ProductInfo CTE. I underlined that reference in red.
- The outer query can refer to any CTE. Again , underlined in red, we see how our outer query referred to both the OrderInfo CTE and the ProductInfo CTE.
Kinda neat, right?
Next Steps:
Leave a comment if you found this tutorial helpful!
If you found this tutorial helpful, make sure you download your FREE Ebook:
FREE Ebook on SQL Server Table Expressions!
This Ebook provides an excellent summary of everything you need to know about the different table expressions we can create in Microsoft SQL Server, including Common Table Expressions. It will be a great resource for you to reference throughout your career as a data professional. Make sure you download it today!
Don’t forget to read up on Derived Tables, too. They are another great querying tool!
Also see my tutorials on Views and Inline Table Valued Functions, which are also table expressions. These objects are actually persisted in the database, unlike CTE’s:
What is a View in SQL Server? A Guide for Beginners
Inline Table Valued Functions: The Ultimate Guide for Beginners
Thank you very much for reading!
Make sure you subscribe to my newsletter to receive special offers and notifications anytime a new tutorial is released!
If you have any questions, please don’t hesitate to leave a comment, or better yet, send me an email!
