Introduction to Table-valued Expressions

Video Transcription

Welcome back everyone. In this video series, we’re gonna be taking a look at table expressions. So previously, we talked about sub queries, particularly two types of sub queries self contained and correlated, the self contained sub queries being a sub query that can actually execute in isolation, so on its own without any dependency with the outside query. correlated query is the opposite of that, where we are referencing at least one attribute or column, and the inner query from the outer query. So we also talked about three different types of return values for sub queries that being single valued and multi valued. So those are the two primary ones that we covered. We also briefly talked about table valued returns. So remember, the single valued query is just as it sounds right, one single value that’s actually returned, so like a number or a string of some kind. And then multivalued sub queries are ones who are going to return one single column, but more typically more than one row. So technically, a single valued return is also a multivalued return. But a multivalued return can have more than one value, right? More than one row. But what if we have more than one column. So we really didn’t talk too much about what happens when a sub query returns more than one column. But that’s going to be our focus for today. So a query that or a sub query that returns more than one column is going to be commonly referred to as a table expression.

A table expression is a named query expression that represents some form of table. And now this table may not be physical in the database more often than not, this is actually going to be a logical construct only. So meaning that it’s a collection of information that has been formed into a table using the query rather than a an existing physical table in the database. Along with that, right, the table expression itself is merely just a reference. So anytime this table expression is actually referenced, is going to be a collection of queries that executes to create that table as a result. So why why the table expression? Well, generally speaking, table expression is not going to actually offer you any performance increases. So your SQL commands are not going to actually run any faster, for the most parts and using table expressions. But logically, they become usually easier to read. So this is very similar to the idea of functions and programming, right? We often use functions in programming to reduce the amount of duplicated code, or code that’s been copy and pasted or repeated, similar kind of idea with the table expression, since we can actually have a named representation of that table expression. That’s typically a complex piece of complex piece of SQL. So more or less, just logical benefits here, so readability and then also reuse depending on the type kind of table expression.

Some of our table expressions that will actually cover here are not allowed to be reused more than once. So that does come with a caveat there. And like I said, very rarely is a table expression used to increase the efficiency or productivity of your SQL code. There are some edge cases depending on the scenario, but more often than not, it’s used primarily for logical readability and construct, the easier construction of your SQL. Other things that include a table expression. So for a table expression to be considered a valid table expression, we have a few other things to consider. So first off, very similar to how we work with things like joins and stuff like that order is not guaranteed unless it’s actually ordered. Now, there is a tricky part with that as well as we’re not actually allowed to use an order by in certain scenarios and will actually show an example of this here in a few minutes. But all columns that exist inside of this table expression must be named. So we can’t have an unnamed column as parts. So Like if we do an aggregate like say average or count or something like that, we can’t just leave count star there, we actually have to say count star as or apply an alias to that column. Unlike a normal query where we don’t have to necessarily name our columns. If we’re not referencing them later.

Similar to an actual table, all column names must be unique. Okay, all column names must be unique. So think of a lot of the qualities of a general physical SQL table to be similar to the logical table expression as a result. Now, our table expression can be used in a lot of different scenarios. Pretty much in any data manipulation statements that we actually write, we can use a derive table there. And we’ll show a variety of different examples of this in action. But usually, the table expression is going to exist, for the most part inside of the from clause. But we’ll show except we’ll show a couple different examples of where we can place this inside of our queries. But different there are four primary types of table expressions that we’ll cover in the this video series. The first one are derived tables, which are most closely related to a sub query. So all of the sub queries that we covered previously, we only covered remember the single value queries and multivalued queries, but we didn’t get to the table value queries. And so that’s what a derive table is going to be. Then we have common table expressions, which are starting to look a little bit like functions similar to views and inline table valued functions. So an inline table valued function is like a view but with a couple parameters allowed with it. But each of these will be a topic in the upcoming videos.

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.

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.

Views and Inline Table-valued Functions

Video Transcription

Welcome back everyone. In this video, we’re going to be taking a look at views and table valued functions. And so up here I have both of the syntax for each. So have the syntax for review and the syntax for inline table valued functions. And I will show the Show More examples of these and will hopefully make a little bit more sense then views and inline table valued functions are provide an extra benefits when compared to derive tables and CTS. So when we were using CTS and derived tables Previously, we had to use them within the same outer, the outer query, right, so the the life of a derive table or a CT exists only within the query, it actually executes. Otherwise, that CTE or derive table doesn’t actually exist. So views and inline table valued functions have a much broader scope. And so they provide the benefit that these are actually stored in the database itself. And so we can reference these in multiple queries in multiple occasions without having to recreate them every single time. So that’s the primary benefit here. So they’re available and stay in the database until they’re actually directly dropped from it. So you can really think of these are, from a view, it’s mostly just similar to a derive table, that we can actually reference more than once. And then similar to inline table functions, we can reference those more than once as well. But we can actually pass parameters to them. So they start to behave exactly as they sound, they start to behave more like functions than just something that we can call. So view is basically a function without parameters.

And then the inline table valued function is essentially the same thing as a view, but we can actually provide parameters to it. So remember, though, just like with our derive tables and CTE, they are logical constructs only, meaning that the actual data that represents a view or table valued function, just the same as a derive table or a CTE, that derived information is not actually physically stored as a table in the database. Now with a view or an inline table valued function, the actual construct of the view or the inline table valued function is stored in the database. But the data that though that those actually present or give access to that’s not separated out from the tables and stored as a unique table is just a logical construct only. So that means that these are actually every time we utilize a view, or an inline table valued function, we actually execute queries as a result of that. But let’s take a look at an example of a few of these. So initially, here, as we start off, I’m actually going to use our just our throwaway database, cc 520. If you haven’t created that already, you can just say, create database, do a database. So you can just say, create database if you don’t have the CC 520 database on your local machine. Otherwise, I’m gonna backspace that out, since I’m already connected to it, but I am going to first create a new schema for our database demo. So let’s go ahead and run this. Oh, sorry, I already looks like I already have already have this schema, then I want to then create a view. But first, let me I want to delete the view that I had for so execute that. But our actual view looks like this.

So here we are, CREATE VIEW demo dot United States as this query here. So as a essentially what we get here with a view is a stored query, write a query that we would normally execute. So I can execute this here, right? This these are the results of that individual query there. But if I wanted to reuse this query in multiple locations, or are on multiple occasions, I can store this as a view and use that. Use that to recall out another time So execute this, now that view is actually created. And to utilize that view, I access it just like I would a table. So from demo dot United States, so United States was my, my view there. So execute this, and voila, there we go, all of the states along with their names. So that’s the that’s a view in its very bare nature, right? The view object is actually physically stored in our database. So if we actually connect to our database over here, I can dive into our cc 520 database here. And then if I expand my views, ah, there’s my view that I created demo dot United States. So this is something that’s actually stored in our database. But the information this data here, the rows of information is not physically stored here. Right, it has columns and everything, just like a table would. But the actual data is pulled at runtime, from the tables that are tables that it’s actually pulling from, right. So just keep that in mind. Right? Our, our view is not storing information, as part of it, our view is not storing information as part of it. And also notes back here with my view, let me pull this back up here. We had this here, right, even though I’m in my CC 520 database, I’m actually pulling from Wide World importers. And so here’s another big benefit that I can actually pull out here.

Now, from our, our query in general, right, we can execute and pull data from not just the current database were connected to, but we can pull from any database that is on the server we’re connected from, or connected to. And so my fully qualify, this is a fully qualified name. So our a fully qualified to table name or object, database objects name is going to be the database, right? So if I, if I hover over here, it’ll say, database, wide road importers, schema, wide world importers dot application. And then if I hover over countries, it says table. Now, this is in contrast to the view that I have. So if I, even though my view is being put where I would normally expect the table to be by hover over that it’s a view object, not a table object. So the data that the view is actually pulling is not physically stored. As part of it. It’s just storing the query. And that query is then executed when this view is utilized. So this works similar to our table or inline table valued functions. So let’s showcase that. Yeah. There we go. Okay. So again, I’m just dropping, dropping what I had before, before it actually runs. So I’m creating this database object that is demo dot state provinces for country. So and we can actually expand these here. So I have no tables here. Yeah, there we go. Okay, so let’s go ahead and give this a run. So create function, demo dot state provinces for country. So this is the name of the function. And then we define the parameters for that function. So the name of the parameter, and then the type. So this is defined just like a variable would be, except that we don’t have to do define, and then the variable name, but we still need the app symbol, then the return type of this function, so returns and it returns a table. And so then as and then here is the value, the thing that we actually return. So we return, we execute this query and return the results of that. So this is similar to what we just did earlier.

But now, actually, actually run this all as one chunk and that’s why I’m actually using these go statements here. These are the batch processing operators. So I tell this is all one single chunk of code that I’m running in at once but It runs this first, and then it creates my inline table valued function. And then it runs this last SQL statement. So let’s go ahead and give this a run here. And there we go, we get our state provinces, our state abbreviations along with the state names. So if I hover over, hover over these here, you can see that it is a indeed a table valued function. And it can run without this. So if I delete all of this stuff here, just like my view, it highlights it in red, because it doesn’t. The IntelliSense doesn’t pick it up. But it’s still valid, right? It’s still valid, that inline table valued function does exist as part of our database. But nonetheless, we get the results out just the same. Okay, so I’m going to clean this up real quick. I want to remove that from my database. So if I run this, again, it doesn’t produce any errors. But in general, right, I do want to highlight the fact that the inline table valued functions and the views just like derived tables, and CTs are logical constructs only, right? Meaning that the the data that they return is not stored in concert with them. So let’s go ahead and create our view here. So here is the view I’m creating. So demo dot United States cities. So this is pairing all of the cities with the state pairing cities with the states from the wide world importers database. But again, though, I’m still creating this view as part of CC 520. Right. So let’s go ahead and give this a run. That created my view. And so let’s take a look at what this view returns Oops, here we go. So let’s pull this up here. And give this query a run. So this is our previous one that we had. This is the just the United States, the States, along with the state abbreviation and state name. And then we also have our new one here. You using the United State cities. So run this right?

I’m actually joining on two views in this case here. But let’s save this for just a few minutes here. Let’s take a look at our original view first. So here, I just have my view, cc 520. Demo, United States. And if I run this again, right, it’s just our CB abbreviations with the state names, but a handy dandy little feature. And this is this exists, and Azure Data Studio, as well as SQL Server Management Studio. But if I highlight the query, and I say explain, ah, so it actually shows every logical processing step that actually happens as part of this query. So the things that happen first are on the far most right hand side here. So I have a clustered what we call clustered index scans. I’ll we’ll save clustered indexes and things like that for a future topic. But essentially, what’s happening here is it is having a join an inner join between the results of our two views. But what essentially happens here is we are having an inner join, that actually happens, but I have no inner join here and my single view that we have, right, so it’s just United States. But if I remember, if you remember what our view looked like, right, remember, our this is the, this is the sequel that my view runs. So this is the sequel that my view runs. Notice we have an inner join here, right we have an inner join between two tables, country C and state provinces. So this is that inner join. So this is the this is the two tables. And if you actually dived in here, you can look at very way way down there at the bottom you see the output list, you can actually see the tables for the table. that is part of this, this database object.

So you can see Wide World importers dot application dot state province for this one. And then for this one, you can see the countries table. So here are the two tables that we’re doing an inner join on. And then we sort them, we’re sorting them, and then running them through, and then they are actually selected. So our select clause, and then we are pulling from our, to our inner join, right, this is our view. All of this chunk right here is our view. And then that is ordered and returned as a result, right. So again, this is the primary the primary reason why I’m showing showcasing this is if this was a physical construct in the database, we wouldn’t have a join here, right, because I have no join as part of this query that I’m running. But since this is a logical construct, when I actually access our view, that SQL query that has the join with it executes as part. So there’s a lot more going on behind the scenes, that happens with views. And that’s actually gets some people in trouble with views and inline table valued functions, and CTS and things like that. But particularly with views, views, and inline table valued functions, a lot of people think that they are, they improve performance, and they’re optimized and things like that. And while the queries themselves that represent or that the views represent might be optimized, but this query is no better than this query here. Right? There is no logical difference between the two, this is still being executed as the result. And so a view does not necessarily give you any form of optimization, they’re just something to be careful about with that. But let’s take a look at our second view that we had.

So our second view we had, we had one to enter joins as part of that. So between three tables, so we have three tables and two joints. So if we execute here, right, we have demo dot United States. That’s our previous one, right? And remember, that had two tables. And then where’s my mouse here, then, and this one, I’m joining United States with United States cities. And so this table here, or this view here has is joining joining two tables. And then this query here is joining three tables. And then those are joined together to form our final results. So let’s go ahead and execute this. And so this gives me all of the cities along with the state abbreviation and state name. And then if I click the EXPLAIN button here, ha, well, on the surface, this query looks really simple. But underneath the hood, we can see that there is a significant amount of extra work that’s happening behind the scenes. So you can see the actual query, query here, and then the all of the different joins that have to happen. So here’s two joins. Here’s the third join, right of the result of that query. And then here is our, our last join, to combine the two views. And so there’s a lot of things happening here as part of this query. So just really driving this home here that our views and inline table valued functions are logical constructs only. And so when we actually utilize them as part of the query, it’s just simply for better readability and reuse of code, just like functions are but again, write the code inside of the function, or the code or the SQL inside of the view or inline table valued function gets executed every single time that it’s utilized.

The Apply Operator

Video Transcription

Welcome back everyone. And this video, we’re going to be taking a look at the Apply operator. So this is going to be our last table expression that we’re going to be looking at for this course. But again, the general syntax of the Apply operator is included up here. But I will of course, show some examples of how that looks. But what is the Apply operator. So apply itself is non standard syntax apply is unique to SQL Server, if you are looking in other SQL based languages, you’ll be looking for what is called a lateral join. But SQL Server calls this the apply operation. But in general, how this operates is apply as a table value a table operator, and so your the left and right hand side need to be table expressions. Or rephrase that. On the left hand side, we must have an actual table and the right hand side of the Apply can actually be a table expression. So it could be a CTE, it could be a T provided sub query or or anything alike, but some table expression on the right hand side. Now, the main benefits that we get out of using the apply or otherwise known as the lateral join, it allows us to do a join on a correlated sub query, right.

So before when we did derive tables, and we even showed a situation with a join on to derive tables, but we couldn’t actually use a correlated sub query right, because a correlated sub query requires a reference to the outside a column on the outside query. And since the table since our table valued expressions that we’ve been using so far have been part of the from clause. No, the from clause has to execute in order for us to retrieve a reference from it. But the Apply allows us to reference that. So I’ll show some examples of how this looks here in just a moment. But there’s two kinds of applies that we may do. And now more so as the anti standard, right lateral join. Generally speaking, a lateral join makes a little bit more sense than apply. But nonetheless, right, we have cross apply and outer apply. And the same goes for the anti standard, we’ll have a cross lateral join and an outer lateral join. But their primary differences here is that the cross supply is more related to what you would expect for an inner join, right, it’ll apply the table expression on the right to every row and the table on the left, right. That’s why it’s applied to applying the expression table expression on the right to every row on the table in the left hand side. Typically, that involves matching on some predicates and things like that. And so that’s where the cross apply, is somewhat related towards what an inner join might look like, the outer apply is closer to what we would expect to a left outer join.

So it’s exact same as the cross apply. So we have the first steps, first step is the same applies the right table expression to every row of the left table, but then it’s going to add the outer rows from the or it adds the rows from the left table that did not actually have result in any rows in the table expression on the right. So when we apply the table expression, in step one, for both cross apply and outer apply, if that table expression results in no rows being returned, that is excluded from the join. Ok. So the row on the left is excluded from the join. Now, in the outer apply the rows that results, the rows that result in nothing be returned after being applied, or after the table valued expression being applied, then those are actually included in step two. So we do the cross apply. That’s step one. And then just like the outer joins that we used to do, anything that didn’t match, we add on the end. And so here, we’re not actually matching on a predicate, but the the expression, the rows for which the expression returned, nothing for those are added on the end. So that’s why it’s so closely related to it. An outer join. Now. Why would we need to use this? Well, ply provides particularly two kinds of benefits over other sub query types or approaches, particularly when combined with top or another words offset fetch. The first one here is that we can actually pull multiple rows, right? When we actually working with self contained sub queries that are that results. Here, we can actually have more than one more than one row being returned. But with apply, we can actually pull out more than one row.

So this provides an exclusive benefit compared to the self contained queries that we were pulling before. Likewise, we can pull multiple columns, even if we only have one single row. So really, otherwise, a scalar subquery can only be used in the SELECT clause. Because these are a single valued sub query, right, we can’t actually use anywhere else. Mainly because it’s only returning one value. But apply provides us extra benefit on top of that, right, we can actually pull more than one row and more than one column, if needed. This will make a little bit more sense. I’ll show an example of these two benefits here. And just a few moments. But let’s take a look at a basic apply first, remember here how we pulled the last order ID for every customer. So this is back in the video where we started talking about scalar sub queries. So again, I can’t have this, this has to be a single valued query, right, we can’t actually have more than one row being returned as a result here. Likewise, this can’t be more than one column, either, because this is pulled us is placed inside of our select clause. But what if we also but what if we want the last orders order date along with the Order ID? Well, we could actually do two correlated scalar valued sub queries, right? So we could do, we can have a sub query for each column name that we actually need to pull, which would work. But again, right, that’s not very efficient, right? Because we’re having to pull the same table multiple times in order to get that information out. Or we can do a derive table. But even if we use a derive table, we still have to have two references. So this is what it would look like as a derived table. Right?

But again, right, we have one row, right, one row here, right, so we have a select Customer ID Max order ID, last order ID from sales orders group by customer ID join on, right enter join on ID, customer ID equals customer ID. So this result here, right, for every row on the left hand side customers, I have one row one and only one row on the right hand side that I’m joining on. But we’re still referencing that table more than once, right, we’re still referencing the table more than once. So what if we did the apply operation. So with the apply operation, we actually only need to pull the table once we have only one reference here. And so instead of using Max, now though, we actually have top right, so top one, and I’ll show an example here, that shows the difference here. But let’s go ahead and execute this query here. Sorry, executed now. So here we go. We have order ID, but now with the order dates. So this is identical to this is identical to the query that we just had before with the derive table. So here’s that derived table that we did, identical in every way. But here we have customers inner join on the derive table, enter join on orders. So I’m actually having to pull all of the data from orders twice in order to achieve the information that I actually need. But here, this is where a table valued expression can actually save me some some more efficiency or get me some more efficiency out of my SQL is that I’m actually only pulling from my orders table once and only once, right? I’m not, I don’t actually pull all that data twice, multiple times. So this is the primary benefit that I get from using apply in this situation. So let’s go ahead and run this. There we go.

And so I can also, by the way, I can also change this to two, for example. That works, right, that’s pretty cool. We get now for every, for every customer, we get the top two, the last two orders, along with their order dates. And so this is the biggest, one of the biggest benefits. So out of those two bullet points, right, one of the one of the big benefits that we get with apply in combination with top versus a scalar valued sub query with like Max and so on. Likewise, we get some performance increase. Because before, if we wanted to do these queries, with just a scalar valued sub query, we’d actually have to pull that data multiple times from the table in order to achieve the same results. But here, we only have to pull the table once. In addition to this, how can we return all customers along with their most recent order of 2015? Only? Well, similar kind of thing here, I can actually add filters, add that filter inside and my sub query here. So we just have our order date between and then in 2015. But what does this return? Well, as General? Sorry, let me run this again. There we go. So this top order, and this is, we get how many rows we get 657 650 7am I missing any missing any customers here? Well, if you remember back a little ways, we can actually show that there are only 663 customers, as far as let’s select count, star. And then. We need distinct here cil. C dot customer. Id There we go. Now let’s give this a run. There we go. So this is the exact same query, basically the exact same count as I get up here. But now if I switch this here to a left join to get all customers. Right, we can see that we have 663 customers in total. So we have 600. And if I go back to enter join here, we have 606 157 customers that actually placed an order in 2015. And then we’ve got six other customers that didn’t place anything.

So how did we how do we actually get the customers out? That didn’t actually place an order? Well, simply enough, right? Since the way I showed down just just a second ago with the left join, we can do an outer apply. Outer apply, instead of a cross apply, give this a run. And if we switch over here, you notice I have 663 rows as a result. And then to actually see the last six the six rows that were added. Let’s go all the way down to the bottom. Here we are. Okay, here we are your all of the customers that did not actually place an order in 2015. Right? All the customers that didn’t place an order in 20 15 This makes a lot of sense. So if we think about the processing order, so switch back over here. So the cross supply is just the expression on the table expression on the right applied for every row on the left. And we only get, we only have the results of rows that actually result in a value or rows being returned from the table expression. And the outer apply, we do the exact same step. But then we have the step two, where I’m adding the rows on the left, that resulted in nothing being returned from the table expression on the right. And so when we apply the table expression here to pull the the top order from the Orders table for that particular customer, and 2015, we don’t get any orders as the results, right, because they didn’t place an order in 2015. But since I’m doing an outer apply, those customers are added back in at the end and step two of the outer apply the outer play operation to be included, just like what we did, what we kind of similar to what we would do with a left join. So this is a big benefit that we can get using an outer apply versus a cross apply.

So said cross apply is somewhat like an inner join. So we’re getting the results of the right table expressions applied to the rows on the left table, and only getting the results where we actually had rows returned from the table expression. And then the outer apply is the same operation but with the addition of the rows that had nothing returned from the table expression. And in those situations, no is substituted in. And so we can do very similar. We can do. If we wanted more than one row very similar to like what we did before, we can say top two, but now we get 12 We get still, in theory, right, we would get 12 if we if they actually placed orders. But since those are actually applied. And since these are actually added after the fact, we actually don’t get to rows for every customer that didn’t place an order, we just get the customers that didn’t place an order. Compared to previously, we get all of the customers that placed there the the customers that did place an order in 2015, we get their top two, top two results. Now, keep in mind though, if we did have a customer then we placed one order in 2015. That customer would only have one row because the inner query right the the table expression here would only return one row as as as it is. But that really concludes our examples for the apply operation in SQL Server. And remember that with the Apply operator, if you’re looking at the SQL standard, or an other database languages like Postgres, for example, you’d be looking for what they call a lateral join.