SQL Tutorial - Difference between CTEs and Derived Tables

hello and welcome to my channel and in this video we're going to be looking at the differences between Common Table Expressions CTE and derived tables I have created some sample data that I'm going to be using in this video and if you would like to follow along it is available in the description below let's dive right into it so I've got a simple query here that's going to just select some data from a table and if we have a look at that data we can see that there are some duplicates in the table so the first thing we want to do with this data is clean it is remove those duplicates I'm keeping this as simple as possible I'm not going to be using any uh string manipulation or data type conversions within the cleaning data stage it is just going to be simply removing duplicates we're then going to aggregate that data by customer and month and then we're going to take that aggregate data and join it back to some details that we need to return within our query if you're not familiar with derive tables or CTE you will see some cards in the top right hand corner of the video with some links to other videos on my channels and I'd recommend you go and watch those first before checking this out one of the other major differences between CTE and derive tables is CTE can be recur Ive they are very good for resolving things like parent child hierarchies within a database efficiently and we're not going to talk too much about that but again a link to the video will be available so derived tables and CTE are both table expressions and it's understandable question uh amongst people who are starting to work with these um types of operations in SQL is one of is the difference and when should I use one or the other well we have derived tables which are declared within the from Clause of a query very similar syntax to what we have with subqueries uh and we'll go through some example shortly uh and then we have CTE which are declared at the top of a query and then we effectively query that Declaration of a CTE so let let's start off with a very simple example where we're going to look to remove the duplicates from this data so to do that all I'm simply going to do is drop a distinct into this query and then to turn that into a derived table I'm just going to hide the results grid from now I'm just going to write a select all from I'm going to open my parenthesis now the inner queries I always like to indent depending on the level uh and then I'm just going to give that an alias as clean and there we start with a very simple derived table uh that is a drive table in its simplest form so we have an inner query we give it an alias and then we're selecting from that query let's have a look at the equivalent within a CTE on a new query window so if I was to do exactly the same uh again I want the same result so I'm going to add distinct and I'm going to put that with so CT always start with and I'm going to give that a name in this case same name is clean and then again I'm going to put the inner query within parentheses close those parentheses and then the next stage is to do a select from the name of the CTE so if we execute that query we can see we get exactly the same results but it's done in slightly different fashion now for both we are able to execute the inner query independently for both CTE and derived tables and we can see here one of the major differences and this is why one of uh the issues with deriv tables that CTE came along to address is readability so because we're we have to refer we have to build the derived table or create the derived table within the from clause and we'll see this as we go further into the operations we perform on this data it's decided it's not as human readable as it could be so that is one of the main issues that CTS were deci were brought along to address was readability so we have here almost an independent declaration so we can see

top to bottom what's happening to the data uh let's make that a bit more apparent um and let's add in a Next Level so the next level is we're going to aggregate the data now CTS and derived tables are in terms of performance are functionally equivalent so what the database engine is going to do is when we write its query it's going to move everything in line so what that means is it's not creating this table it's not creating distinct copies of these rows into a table and then referring back to it it's taking the query rewrite as a whole and just it folding that out uh and evaluating that so what we want to do from this data is we're going to customer uh we're going to group by money month use the month function we'll give that an alias as mot MNT H and then we're going to do a sum on the amount column and we'll Alias that as total and then we're just going to group by customer and month so that's more represent ative of what we typically do within a derived table or a CTE we' perform an operation on the data and then take that the result of that the table expression and perform another operation on the data so that's where CTE and derived tables both come in is where we need to perform multiple operations on the data so I'll just add the equivalent for CTE now I don't have anything going across years in the data like I say I'm keeping it simple and that's why we can just use the uh the month rather than having to look at the years it's the data is across the same years Group B so again we get exactly the same results so what you want to be asking yourself as this point is which one is more readable um and as we go further you might see that CTE actually become maybe a bit easier to work with but the last thing I want is for you to go away from this video and constantly just use CTS all over the place deriv tables still have their purpose I still use both in this example I'd probably typically use a derived table because for me I think it it's easier to work with but when we get into further levels of nesting or the example we're going to go through at the end where we refer back to the same table expression multiple times you'll see the benefits of CTE so if we take this data now and say we want to add some detail to it so we're going to Nest another level so that aggregate is going to be another derived t table built on a derived table so this is called nesting so if we take what we want to return is customer uh month we also want to return customer type so we're taking this aggregate data and we want to return some details but we need to remember that there we haven't cleaned the data within a table so we need to join to another version of the table so this is we're going to be taking this derived table this nested deriv table and joining to another derived table so we want start date we want month and then we want Total and want that from and again I'm going to add a layer of indentation to represent an inner query I'm going to Alias that as aggra so we're taking aggra and we want to in join to another derive table which is almost going to be exactly the same as what we did here um but we want to take out let's just align this a bit better so we want customer we don't want location um we don't want date and amount we just want to return those

unique values and we'll Alias that as detail we'll give these the aliases that's coming from Agra that's coming from detail and if you can't see these queries great on screen uh I will add these again into the description below so you can copy and paste those into a query window or simply a notepad file just to have a look at them so we're going to be join to detail on customer so if we go ahead and run this query now so we can see we've got the results so we've got each customer we've got a couple of customers that go across months and that's why we've got duplicate rows for them um the query results is really not what we're looking at here um so to get this just on the one screen I'm just going to zoom down to 70% so as we can see that's our final derived table query um so we we' got a couple of nesting levels of derived tables and we've also got a deriv table that we then joining that other Drive table too so there's there is a lot going on in this query but you can see from um a complexity point of view if you was to come and see this within a query or a store procedure there's a lot of complexity going on there you'd have to you know it functionally you start here and you have to have a look at what happens to that data again and I've definitely seen a lot more nesting levels than three in some queries one of the major benefits of derived tables though over CTE is the more levels you Nest the more you can keep querying very easily so we can again we can execute the most in a query uh and then we can execute the sort of m in a query if you'd like to call it that very easily to see how that data builds up let's have a look at what that would look like uh in terms of a CTE um so what we do here is we're actually going to be declaring a another CTE um so we just add a comma and then this is going to be Agra and we are going to indent this as I like to do just get rid of that semicolon for now and then we can have select all from Agra so run everything so there's our Aggregates so this is the difference I was just mentioning with CTE so it's very easy to run the first one however if we want to run the second one we've got a couple of options one option is we comment this bit out we would actually need to leave that comma in there so we could run that or we just need to keep changing the select statement as we've done there so we can refer to clean if we wanted so even if it's processing the Agra CTE we don't actually have to return data from that okay so we've got our Agra we then want to do a join back to the details so again we could have another CTE here as detail and that would be almost like clean and take out location uh we just taken out some columns here that would cause further duplicates like a one to many join and then we can select all from Agra as a in a join detail is B on a

customer equals B customer and then from a we want a customer I'll just uh increase the size of this a customer and we wanted B customer type B start date a month and a total if we execute the whole thing we get the same results as the dve table but does that look uh a bit cleaner and more readable to yourself uh and that's the question you need to ask when you're looking at NES in multiple levels we can see at a top level if we just wanted to perform one operation so typical example is we're going to group by and then join back to the table if we needed to do that it would be I think to be honest down to personal preference whether you like derve tables or CTS um like I say I use a mixture of both and you can combine derive tables and CT here so if we took out the detail level we could actually say we could actually put a derive table here it should be customer type start date y That's exactly the same results so there's one way of combining CTE and derived tables so I bet you're hoping from this video you're like what well what do I need to use and the answer is always it depends but I would try out both you can see the benefit of CTE we we're looking at declaring something at the top or working through that data rather than here where we've got okay this query when I look at this query as a whole this query is executed first then that query whereas here you can see okay this is what happens to the data then this we get this so it depends on how you look at the data um nesting derived tables can get quite complex so it just depends on how many operations you'll need to perform on the table like I say I don't want you to go away and just think I can use CTE for everything called deriv tables for everything you can uh that is an option um but have a go with both um now the other thing we can move on to and the other major uh benefit of CTE so the first being readability of CTE the second is actually when we need to refer to the same um CTE uh or derive table multiple times so we'll go through another example of that now um and I'm going to shortcut the process just to make that simpler so I'm just going to uh drop this data into a table uh for us to so I'll just create a customer sales clean version have that there so I'll just have a quick look at what that data looks like should be a lower row count yeah cool so let's take that onto a couple of uh new query windows and let's work through an example of let's say we want to compute the so do the month on month comparison for customers so what we want to do here is customer uh again we're going to use our month so pretty similar well pretty much exactly the same as the way we aggregated the data before we're just not messing around with our um cleaning stage cuz we dropped that into another table or a d duplicating

stage uh some uh amount as total so let's say we want away add in the group boy let's say we want a way of um if we have a look at Paul Kirkland here we can see spent money in month five and month six so let's say we want to do a comparison between both months so how we do that within a derived table um we want to write out a month on-month comparison um and this should really show off uh some readability differences is what we're going to have to do is we're going to do a select all from and this is how we're going to build out this Qui query I'm going to indent this as it's previous if I can spell previous and then I'm going to left out a join exactly the same data uh should be we can clear why I'm doing this very shortly and that's going to be as current on previous customer equals current and previous minus one equals sorry previous month minus one equals current so if we have a look at uh then we go so customer will be both in same in both tables and then we go current so total minus previous total that's difference and if we have a look at that so we've only got um we've only got two customers that spent money in both months but what we can see here is we're having to write the same query out multiple times so within a deriv table you only refer to you can only refer to table that you've declared uh once whereas we'll see the functional equivalent with CTE and it's much less verbose so if we write out the equivalent of that within a Common Table expression so we wanted Agra as select customer uh month date as M and then some amount as total adding a group by customer and month and then we can do select all from Agra as a left out to join Agra as B and we'll just a this that better as we've done within the

drive table previous so you can see here I'm actually referring to the same CTE multiple times which I couldn't when I was working with the derived table that'll be on previous customer current customer and previous m n minus one equals current. mnth I'm we going to select current. customer and then current total minus previous do total okay and we get exactly the same results and we can see there another benefit of CTE over derived tables is that we can refer to the same reference multiple times um yeah and we can see there's that's a much succinct piece of code there it's a lot less ver boast there's a lot less going on it's easier to tell than when we look at the equivalent within derive tables there's a lot more going on there we have to effectively run the same data against the same table twice now that doesn't mean within CTE that it's going to again it doesn't save this result so we're not going to see any performance benefit really on that unless that results result starts to be saved um but it is just easier for the eye to see um so the two major benefits of CT over derived tables are readability and ability to be refer to the same reference multiple times uh the benefit of derived tables over CTE if we look back at that previous example is it's easier to keep building on top of deriv tables and having a look what's happened with the data uh if we have a look at that within CTE so it's easier to look at the first layer if we wanted second layer we'd have to actually comment this out here or change our select to see how that data is flowing through the CTE so you don't always get a clear visibility of what that data looks like at each stage of uh of processing effectively within the Expressions um so to answer the question really what should I use derived tables or CTE well CTE are more modern like I say they were invented to address the problems of derived tables so in general you'd probably be better off using CTE but I wouldn't dismiss derive tables all together for for simple things or you may just prefer that approach um like I said there's very little in terms of performance between the two they're both executed in exactly the same way uh let me know your thoughts in the comments below subscribe to the channel if you haven't already um thanks a lot for watching