How to create Derived Tables in SQL Server

hello and welcome to my channel i am bearded dev in this video we're going to be looking at derived tables we're going to be looking what derived tables are why we should use a derived table and how to create derived tables let's head over to sql server management studio and go through some examples okay so you've joined me in sql server management studio for this demonstration i am going to be using adventure works 2019 it is a database that is readily available online so if you'd like to vol follow along do download a copy of the adventureworks.pac file and restore it also the code samples will be in the description so if there's anything you're struggling with you can refer to the description for the code that i'm actually going to be using in this video now we're going to be starting off with looking at what a derived table is so i'm just going to start off with a very simple query which is going to be selecting the customer id and the total due from the sales dot sales order header table okay so now what we're going to look at is how we can turn this into a derived table and the derived table is often referred to as a sub query and it and it kind of is but the difference is a derived table appears in the from clause so what we're going to be doing is selecting all from and then we're going to just wrap our original query in parentheses and i'm just going to indent that to show that clearly so we can see here similar to a sum quer sub query except that this returns an actual table result set so we're going to be performing this operation first so we're going to be selecting customer id total due from sales dot sales order header and then we're going to be selecting everything from that result set now we can see here we have a red squiggly line and if i go ahead and execute this we do get an error message to say we've got an incorrect syntax now the reason is is because we actually have to give our derived table an alias and i just commonly alias it as d so if we go ahead and execute that now we'll get the correct results now within a derived table within the parentheses we're okay to go ahead and execute that query as normal so the parentheses are representing perform this operation first so first of all sql is going to know that it needs to select customer id total due from sales dot sales order header table and it's going to be selecting all from there and then i can change this select to how i would want it so let's imagine i just wanted to select the customer id for argument's sake so i'm just going to select the customer id from this result set and if i go ahead and execute that i've just got the customer id returned now from that result set so that's a very quick example of what a derived table is so very easy to write remember we have to give the table an alias we can execute the inner query within the parentheses independently so that's a good benefit it appears within the from clause of our actual query itself that's the operation that's performed first okay so we've we've seen a quick example we now know how we'd go about writing a derived table let's move on to practical use so why would we actually use a derived table now let's imagine that we want to perform multiple operations on our our table so let's imagine in this case for example we want to find out the average of total customer spend with us so we're unable to write a simple select statement that goes give me the average of this of the sum by customer so that's where we could use a derived table that's quite a useful way of looking at derived tables so if we change our inner query to return total due by customer id and we're free within this inner query to perform most of the operations we would do in a normal query but it has to return a relational set and what i mean by that i'll come on to shortly so we're going to start off with our inner query within our derived table where we return the total by customer what we then want to do is take that total for each customer and calculate the average so let's calculate then average total due as average total and we don't need any average total because we've we've actually aliased that within here so because we've aliased it within here in the outer query we need to refer to the new column names so let's execute that query and we can see we've got our result set so that's a typical use case for derived tables similar to ctes i'm not going to go through the differences of derived tables and ctes in this video but i am doing this as part of a short series all about derived tables and as part of that there will be a video on the differences between derived tables and ctes i'll also do a video on joining to derive tables and also nesting derived tables so we can also nest derive tables but things do get a bit complex but that will be coming soon to the channel so that's a typical use case for a derived table so think about other operations you need to perform so perhaps you like to use window functions so window functions happen within the select but then you want to query those window functions so you want to apply filtering perhaps so what we could do is wrap that window function in the inner query in the derived table and then select and apply filtering to that now let's talk about some some common errors with derived tables so mostly the mistake i make is forget to alias the drive table and the error message isn't quite clear it just gives you an incorrect syntax error it doesn't say what's actually missing so that's a common mistake i make the other one is that we need to give our columns a name so we need to actually name our columns within the derived table as let's take off this this calculation and we'll just change this to a select all with an asterisk so we can execute the the inner query in this case because we don't need to return the column name but when we've got it within a derived table and we try and do that we'll see that we've got an error to say no column was specified and there's a couple of ways we can actually give our columns names so we've seen the typical way we give it an alias but we can also do it in a table definition here so if we say as d and then we can specify a list of columns so that's quite interesting so we've opened another parentheses there and we've defined the columns within our derived table here so we go ahead and execute that and that would happen as normal now that's not something i often do but it might be something you want to have a go at i prefer to alias each column individually but let's have a look at what happens if we try to use both so let's give this let's call this sum total now let's this is going to be interesting to find out so we've aliased it as sum total here but we've aliased we've defined it as total here so which one of those wins and it's actually the definition here so even though we've aliased it as sum total here this is overwriting that with a new column name the other one is we can't actually use an order by so remember i mentioned earlier you can pretty much do anything within the drive table but it's going to return a relational set now an order by will break the relational set uh unless you're using something like top or offset and fetch so if i add an order by customer id into our inner query and execute it we'll get this error saying the order by clause is invalid and it actually lists out where it's invalid views inline functions derived tables sub queries common table expressions uh unless top set offset or for xml is also specified the last thing to mention uh about in this introduction to derive tables is they are executed in line so what does that mean well let's just take off that for now and we will remove this so we're going to start off with a very simple derived table that's just going to select the two columns from our table so we're just going to select those two columns and then in the outer query we're going to perform the aggregation so we're going to perform sum total due as total so when we read this query what's actually happening is from the table we're returning customer id and total due we're then taking that result set and we're saying for each customer sum that total due amount so of course we can we can simplify that we can simply write select customer id and then sum total due as total and this is directly from the table so there is no need for us to use a derived table here but it suits what we're going to look at very well we're going to look at what we mean by executing in line and to do that we're going to turn on the actual execution plan so there's a few ways we can do that i'm going to click include actual execution plan up here where you can press ctrl m on your keyboard or click on query and include actual execution plan let's execute both of those uh just add a group by customer id so the queries execute okay so we'll get exactly the same results from both queries they're doing exactly the same operation but it looks in the first one as if we're asking the database engine to perform more work it looks that way let's have a look at the execution plans and i'll just drag this window up so if we have a look at the execution plans on first look we can see the operators are exactly the same now the first query is a derived table and what's interesting is to see the query cost so between the two it's exactly 50 50. so it's performing exactly the same amount of work for both queries and this is how when working with drive tables ct's views or in-line table valued functions sql server becomes very clever it expands the logic that you're trying to do into it as if it was a normal query so what it's actually doing here is just performing the operation as if we wrote it as the second query it knows it can ignore what we're actually telling it to do it's trying to take a shortcut and that's what we mean by in-line execution really hope you have enjoyed that video as mentioned keep your eye out for other videos on derived tables coming to the channel shortly check out my other videos if you haven't already subscribe to the channel and hit that notification button to be made aware of when new videos are uploaded thanks a lot for watching

Channel: BeardedDev

Views: 1,355

Rating: 5 out of 5

Keywords: beardeddev, bearded dev, sql derived tables, sql server derived tables, derived tables in sql server, derived tables sql, when should you use a derived table, derived table vs cte, how to create derived tables, derived table examples sql, sql derived table example, derived table tutorial sql, sql dervied table tutorial, how to create derived tables in sql server, sql tutorials derived tables, what are derived tables in sql, should i use a derived table or cte, derived table

Id: nxozAGkKFrg

Length: 15min 1sec (901 seconds)

Published: Wed Mar 10 2021