Derived Tables

Video Transcription

Welcome back everyone. And in this video, we’re going to be taking a look at derived tables. So derived tables are actually better known as a table valued sub queries. But with the restriction that a derived table can only be a self contained sub query, it cannot have any relation to the outer query or be a correlated sub query. So, derived tables are always going to be defined in the from clause and the outer of the outer outer query. But the scope of that is a little bit more restricted. So a derive table can only exist inside of as part of the outer query. So once that outer query is actually finished, that derive table no longer exists. But there are some other weird quirks with a derive table as well. And we have a couple different kinds of aliasing that we can utilize as part of this. And I’ll show both of these examples in line and external and what the differences will will be, we can actually also nest derived tables inside of each other. So we can just like what we can nest sub queries within sub queries and so on, we can also nest derive tables. But there are some limitations to these derive tables and use. So the primary limitation here is that inside of our FROM clause in the outer query, a derived table can only be operated on once. So that table can be referenced a single time as part of a join, and then it cannot be referenced again. So in isolation, so once that derived table has been joined with another, the original derive table can never be referenced again. So there is that primary limitation on the use of a derived table. But let’s take a look at a few examples here to see what these actually look like.

So, and our most simple use, let’s take a look at this query here. So we have a just simple slug star just as a show you as a quick example here, but we have a sub query here as part of our FROM clause. And notice that this is table valued. And I know this is table value, because we have more than one column. And we could potentially have zero or more rows. So that’s the primary difference between single valued and multivalued is that the table value will have more than one column. But in a multi value and single value sub query, both of those are only restrict or restricted to only a single column of data. So that’s a quick and easy way to tell the difference between a table valued sub query versus either the other two that we’ve already covered. But anyways, this is a relatively simple query that’s going to get all of the customers that are a computer store. So let’s go ahead and execute this. And we can see our results. And let’s see, we have 51 rows or so 51 customers that are coming, are categorized as a computer store. Now, let’s take back a few things out of this query particularly what happens when I start to violate some of the restrictions that we actually haven’t placed for table valued sub queries. So remember, we must have a name for table vide sub queries. So we cannot skip on the alias. So if I take out the alias here and run this again, Ah, sorry, let me take out that. So you see that we actually get an error as a result, right? Because our, our sub query must have a name associated with it, right? We can’t just have this table out there. Without any name, if you think about how you how we created tables, and defined tables as part of a actual database, right, our tables, all of our tables must have a unique name, all of our columns must also have a unique name. So that means that if I have a column here, that is not named or example, I have an if statement, if and only if here, if the account opened is before 2015. Then they are loyal because they’ve been with us for a while. Otherwise, they’re considered as a new customer. So it’s relatively simple piece of SQL here, but notice I have no alias associated with this. I have no alias associated with that. So that’s fails, if I run this query by itself. And remember, I can run that query by itself, because it is a self contained sub query, we can’t have a sub query that is correlated, that generates a table, a table value as a result. So self contained queries only here.

But you see that this inner, the sub query executes just fine by itself, right? Because it is value valid SQL, customer ID customer name. And if I don’t have a column name, SQL defaults to no column name as a result, but as a whole, if it’s used as a derive table, remember our actual physical our tables, whether it be physical or logical, must, our columns must all be uniquely named. So with that, what happens if I name this column as see here, say or as, here we go. Customer category name. So what happens here? There we go. So everything is unique, right? Everything is unique. But what if I now take and add the actual customer category name column, so from the actual customers table, so customer categories, dot customer category name, and then comma there. And then if we rerun this query, ah, now we get an exception, right? The column customer category name was specified multiple times for customer store customers, right, that’s the derived table that we have. But again, right just like a physical table, our logical table must have unique uniquely named columns as a result. So I also talked about some weirdness with ordering right. So order of a derived table is not guaranteed, just like the order of a physical table is also not guaranteed. But with a derive table, we actually cannot have an order by we cannot have an order by as part of a derive table, because a table innately is cannot be ordered, right? It’s, the order is not guaranteed. So if I run this inner query by itself, again, right self contained that can execute in isolation, we can see that it works just fine. But when we execute it as part of as part of the whole, we get that exception, saying that we can’t actually utilize the order by as part of that.

So we can’t actually order our table. But just some notes on some common exceptions that you may get when you first start doing table valued expressions, particularly with derive tables. We also mentioned before about different types of aliasing. So inline versus external column aliasing. So let’s take a look at a difference between these two. So in line column, aliasing is just as you normally expect, right? We for each of the columns, so select year as order year count star as order counts, this customer ID as customer accounts, so on and so forth, right. So this is our normal aliasing that we have been using in all of our videos up to this point. So this is what we refer to as inline aliasing. Now, external, is a little bit different. So if we run, show you this query here, shift this down for a second. But we can see the difference between these two. Now, both of these queries are identical in nature, except down here and the second query, I actually have this little notation here. So this is known as external aliasing. So my sub query itself, I have no defined, or I have defined columns, but my columns are not actually named. But if I look down, if I go down into where I alias, my derive table here, I actually specify the column names. I specify my column names here. The syntax looks very similar to back when we and started inserting fake data into our the initial database that we created with the schools. When we did insert statements and things like that we could specify the column names associated with that for the actual given table.

Similar kind of syntax We’ve used here, but just remember that the syntax that we use up here using inline column aliases, and external column aliases are both valid syntax. And there really is not necessarily an innate benefit of one or the other, unless you are utilizing these columns down here. So like, if you wanted to, if you had anything that you wanted to actually reference the Select by, like, if you’re using top and stuff like that, then you may want to utilize inline column aliases here. But that goes along with different table expressions that we’ll be covering later. So we also talked about how we can nest our derived tables within each other. So let’s take a look at an example of that. So here have order your customer accounts. And then from and I have this big derived table called a cc. And then inside here, I’m picking out the order year and the customer counts from also a derived table. So I’m pulling the order year and customer ID from the Orders table. Right. So this looks to be relatively complicated as a part, but here we see that we just get the number of customers that we have per year here. Now, where our customer count is at least 650, this can be rewritten again, like many of our other types of sub queries, this can be rewritten using joins. But nonetheless, this does just show that we can nest derive tables within each other just like how we can nest other types of sub queries in with each other.

So, what happens then, when we try to reference a derived table more than once I mentioned before that once a derived table has been joined on, then it cannot be referenced again. So here, we have a derived table here. So this is called cu r. So this is the the customer counts per year here. And that is left joined with the order year and customer count. Again, p r e previous order year equals order year plus one. So it’s basically combining the current year with the previous year. And so let’s go ahead and run this. There we go. So here we have both joined together. And again, right we have a left join. And so here for 2013 2013 was our earliest year that we had sales for and so we don’t have a previous customer count there. And then we showcase the following years, the customer count and 2014 versus 20 are in 2014 versus 2013 and the difference between the two. So, pretty useful query as functionally speaking as we could see, but if I tried to join by tried to separate this try to run this Ah, right, you see, I cannot actually join, I can actually do a self join on myself, right. And previous previous videos, we saw that we could actually do self joins, which is a very useful query. But in this situation, I can actually use a self join on a derived table, because the derived table is referenced as part of the joint already and cannot be referenced more than once. Right? So that is the limitation here that we see with a derived table. But that’s going to be it for our examples on derived tables.