Common Table Expressions

Video Transcription

Welcome back everyone. In this video, we’re going to be taking a look at common table expressions, otherwise known as CTS. We can see the syntax of a common table expression here on this particular site here, but it’ll show some examples that will make this a little bit easier to read. So, as I mentioned, common table expressions are also known as C, T E’s. They’re very similar to derive tables write, similar in nature that the scope is limited to the outer query. But the benefit of a CTE is all of these things here. So probably the biggest benefits between a CTE and a derived derived table is that we can reference a CTE more than once. So previously, when we talked about derived tables, we, we were shown a derived table cannot be referenced more than once. So we can’t do a self join, for example, on a derived table, but with a CTE, that’s going to be possible. Along with derive tables, we can also do in line and external ABC and CTS, we can also utilize more than one CTE in the same query. And CTE is can also be recursive. We’re going to save recursive CTE, though, for a nother video, that’s a more complicated topic. But just like you can call and reference a function within a function, we can have a CTE that references itself. So recursion, but nonetheless, let’s take a look at a few examples of a CTE.

So previously, this is what we ended on with a derived table. So let me undo this bit here. So we had a derived table. And in order to reference a derived table more than once, we actually had to populate, to actually redo the derived table twice, right. So in order to join this derive table on itself, we actually have to do the derive table yet again, on the right hand side of the join. Now with a CTE we can actually get around that issue. But let’s take a look at a simple CTE. First, let’s take all of this out and replace it with CTE. So here we are. Now, we are going to declare a CTE very similar how you might see a function in your favorite programming language, either Python, or Java, or even C sharp, we have a simple variable up here, which is going to be our customer category name. And then here is the beginning of my CTE. So this chunk right here is my defined CTE. Notice, I don’t have a semicolon at the end here. That’s because this is all one SQL statements right here. The scope of my CTE only exists up till this point, right? The scope of my CTE is only in this highlighted portion, if I had other SQL query is down here, but this semi colon still existed, then the scope here ends with that. But with and then this is the name of our CTE customer store customers, or computer store customer sorry, not we’re using an external aliases here. So we’re defining our column names externally from our sub query. And so with this CTE as and then here is our sub query within it that defines our CTE. And again, this is a table valued expression, because we have two column names or two columns, right?

So from sales customers see enter join sales, customer categories on customer category ID. So this is very similar to a query that we saw before with our with just a derived table. So let me pull let me bring this back up here. So you can see that so remember, this is the one that we had before with just a simple derive table, doing the exact same thing, but now I’m just doing it as a CTE and in general, in my opinion, CTE I find site a CTE is much easier to read and utilize versus derived tables, just because it doesn’t really busy up your main outer query here, instead of compared to the derived table, which all the SQL is built in, there are nested inside. So CTE is in general are a little bit easier to read. And logically, there is no difference between the two. As far as performance goes. So let’s go ahead and execute this so we can see our results. So very similar to our previous one, we have 51 customers, that are computer store customers. And the benefit here is I can actually change this variable name to get different categories, if I wanted to. But big point here, again, is the importance of the semi colon, we don’t have a semi colon here, because if I put the semi colon here, notice now all of this is bread, and all of this is red, because this CTE no longer exists, that CTE is coupled with the outer query which is actually beneath it. So just as a just as a friendly reminder there. But in comparison here, as I mentioned, we had external aliasing there, we can also do internal aliasing, since my inner columns here are actually named already, I don’t actually have to specify the column names as part of my CTE.

So if I run both of those, I actually get the same, the same results as parked, just like what we can just like how we actually define our multiple variables in SQL, we can also define multiple CTS and utilize them as part of our query. So here we have a two CTS order year CTE and customer count si t. So again, this is the exact same derive tables as I had previous previously in the in the last video, when we talked about derived tables. So if we execute this, we can get the order year paired with the order counts, just like we did previously. But in general, in my opinion, this is significantly easier to read, when you’re when you’re inspecting and going through SQL code. So I tend to err on the side of using CTS versus derived tables. But both are logically equivalent. Unless you’re talking about referencing more than once, then we need to use a CTE instead of a derived table. Just to showcase that issue here. We have one single CTE here from customer count CTE. But down here, I’m doing a little bit more complicated query. So this one is equivalent to this query here. So remember this, this really long query that because I had to get around the idea, or I had to get around the limitation of a derive table because I can’t reference the same derived table more than once.

So I can’t do a self join on a derive table. So this query in general is really difficult to read and very bloated in general. But this is already significantly fewer lines of code. And it’s also much easier to read as a result. So we have customer count CTE, but now I can reference that CTE more than once. So from customer count CTE cur. LEFT JOIN customer count CTE previous on order year equals previous order year plus one. And so we get the exact same result as my previous execution as my derive table, but we end up getting a much, much better viewpoint, our SQL ends up looking much better as a result, so a lot more user friendly and readable overall. So those are the primary benefits to a CTE versus a derived table, even though logically they achieve the same goals. But CTS do have the benefit of one being able to be referenced more than once. They are, in general, a little bit more user friendly to read. And there’s also a couple other benefits particularly with ACC being allowed to be recursive as well. But as I mentioned earlier, we’re going to reserve that topic for a nother video.