How to Write a Simple CTE Query

Let’s start slow with a simple CTE query demo for easy absorption.

Transcription of video:

So let’s take a look at how to write a simple CTE query. So what I have is a test database here that I’ve set up called CTE demo. And in that I’ve got a couple tables one’s called royalty that has a bunch of the British Royal Family. And it has some numbers in there that identifies who their mother and fathers are. And then we have a table called departments, which is departments from like an outdoor store that has campaign and cycling and snow sports. And it’s a hierarchy here and that you can see that some of the departments have a parent. So under the Department of camping with an idea of one, here’s the idea of one that references, tents, backpacks, sleeping bag, and cooking, are all part of the camping department, we’ll just take a look at what we have in the department’s table, select star from departments. And there are 17 departments listed there. Some of them have a parent listed, and some of them are top level departments that don’t have a parent, then we’ll take a look at what’s in the royalty table, which we’ll use a little bit later.

But in here we have number of British royal family and who their mother and fathers were through the sort of family tree structure. Okay, so let’s first start by taking a look at a drive table. Now what a drive table is, is often referred to as a sub query. So we’re starting out with a select statement. And inside of that SELECT statement, instead of selecting directly from a table, we’re selecting from a sub query that references a table. The sub query is is referenced as an alias of just dept for department. And then we’re selecting from that that’s really useful when you want to do other things or calculations on the data that’s coming back. It’s bit silly the way it’s represented here, because it doesn’t really give any value beyond selecting what’s on the inside. But it’s sort of building the foundation for what we’re going to do here with a CTE.

So the common table expression equivalent of what this select statement does, is shown here, where we have with with a semicolon in front of it just to be safe, department CTE, we call out the columns that are going to be returned. And that is optional, those columns as and then we have inside of parentheses, our query, that’s the CTE query, and then we select from that department CTE. And when we run this, we get similar results to what we saw on the drive table query above. So what happens if we don’t have that semicolon at the beginning of the CTE, here’s an example that shows multiple statements, select star from departments, and then there’s no terminating semicolon on that. And then it leads right into the CTE with the with statement. When we run that we get an error message that’s incorrect syntax near department CTE. And then recently, Microsoft in the newer versions of SQL Server has updated the error messaging. And they’ve said if this is intended to be a common table expression, you need to explicitly terminate the previous statement with a semicolon. Well, it used to be on older versions of SQL Server that it simply said syntax error without all this extra detail that makes it easy to see what’s going on there.

So just the reminder, keep in mind if the CTE is not the first name in the batch, it must be preceded with a semicolon either at the beginning of the CTE or the end of the previous statement.

More from Stedman Solutions:

Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!