Introduction to Subqueries

Video Transcription

Welcome back everyone. In this video we’re going to be focusing on sub queries. Now up to this point, we’ve covered how to do single table queries. Multi table queries with join. So Inner Joins cross joins outer joins. But in this video, we’re going to be focusing on how we can nest queries inside of each other. But before that, let’s review a few things. Remember our processing order of SQL and the order that we write SQL is different. So the way we have to write SQL uses order of statements is listed here. But they’re processed, starting from the from clause, because before we can actually enter any of the other phases, we have to know what data to start with. Where allows us to filter those filter that data row by row, then we can of course, group that data together, and then filter those groups. So we have a the where clause being a row by row filter, the having clause being grouped by group filter, and then we can finally pick out which columns that we are projecting out and into our results, we can specify that we only want distinct rows from the from the result and also remember, distinct can also be utilized as part of aggregate functions as well, like Distinct Count, or count distinct sorry, then we can order our results as well as sending or descending order, ascending by default. And then finally, we can use top to restrict the number of rows that’s actually returned. So let’s say top five or top 5%. Offset fetch is the SQL standard of top remember, top is only native to Microsoft SQL Server. Fetch and offset are ancy standard and will be present in most SQL languages that you encounter.

So remember that joins are table based operations, we can take two tables as input and produce one table as output. That output table can then be utilized as part of a multi join query. So if we want to join more than two tables together, we can feed the results of one join into a another. Another join, we covered three types of joins. So far, cross joins, Inner Joins, and outer joins. All of these differ a little bit in their processing phases. But remember that the inner join builds off of the cross join and the OUTER JOIN built off of the inner join. So remember, cross joins are going to only have one single face and the base join. The Cartesian product is what is produced as a result of the cross join. So a row each of the rows and table a paired with each of the rows with Table B, all the different combinations there, then the inner join builds off of that cross join. So we have the Cartesian product again. But then we add a filtering step, just like if we had a where clause with it. And then our outer join is a Outer Joins build off of both of them. So we have a cross join first, and then an inner join. So we cross join, filter. And then we have a preservation step, where rows that were not matched, and the filtering phase will be added back in and depending on if it’s a left join, right join or full join designates which table is going to be the preserved table or both. But let’s focus on this videos topic sub queries or nested queries, you can kind of think of a sub query as if it was a nested loop in a sense, so you have an outer loop and an inner loop. So with that, we have an outer query and an inner query.

So the inner query is going to be embedded inside of the outer query or the caller. So the inner queries results will be returned the outer one, and we have a couple variations of this as well. So how this actually functions is going to be logically different depending on the types of values that are returned. And whether or not the inner query is dependent or independent. What that we do have two types or two variations of sub sub queries, self contained, where there is no dependency on the outer query. So the self contained query can execute all on its own without any information from the outer query, a correlated query which has a dependency on the outer query. So this is if we have an inner query or a sub query that depends on a column that is fed in from the outer query. So a correlated sub query cannot be executed on its own. But with each of these we have generally three types of returns a single value multivalued or table value. So single value just as it sounds, it’s a single value. A multivalued is a single column but zero or more rows, so it can return no rows, but it can also return more than one. And then we also have a table valued sub query, which can return one or more columns and zero or more rows within that, but and the following videos we’ll take a look at a bunch of different examples of self contained and correlated sub queries.

Scalar Self-contained Subqueries

Video Transcription

Welcome back everyone. In this video, we’re going to focus on self contained sub queries. And specifically examples of self contained sub queries that return only scalar, or single values. So self contained sub queries, if you remember, have no dependency on the outer query. So we can execute these queries in isolation, without any without any information from other queries. Logically, self contained sub queries are only executed once. And then that result is then utilized by the outer query. So the inner query executes first, and then that result is then given back to the outer query. But that only happens once. So if you remember, there are multiple types of returns that we can get from sub queries. So single values, multi values, and table values. For now we’re going to look at the single value or scalar sub queries. So scalar sub queries are self contained scalar sub queries will return a single value as a result. And that really allows it to be quite flexible, because we can use that pretty much anywhere in our query where a single value expression is allowed. But we may run into some problems here though, if our sub query doesn’t return a single value.

So if what happens, I know is returned, right? So if no results of the sub query exist, so we get no, and that can provide some issues. Because remember, Knoll is treated a little bit differently than most languages. If a sub query that is used as a single valued sub query returns more than one value, that query will actually cause an exception to be thrown. So this can provide some other headaches, because this syntactically A would be correct. But when the query actually executes, if there are more than one row as a result of that inner sub query, that error is produced. But I’ll without further ado, let’s take a look at some examples of self contained scalar sub queries. So I’m going to start off initially with this query here. So select Max order ID from sales orders. So let’s tab that in. And if I execute that, we see the last order that we had was 73,595. So I can capture this result and utilize it using variables. So something like this, remember, to declare variable, we use the declare statement, the at symbol denotes the variable name, so at variable name, and then after that, we have the type, so in this case is an int, and select Max returns only one single value because our select clause, that’s an aggregate function, and we don’t have multiple groups, so we only have one single result.

So if I run that, we shouldn’t get any errors, executed successfully. And then if we select, let’s, let’s look at that value real quick. So select at last order ID. And run this. There we go. So there’s the value of our sub query. But now that we actually have that value stored inside of a variable, we can actually put that anywhere, we have the option to utilize a single value inside of another query. So here, I just have a select Order ID ordered, ordered dates, customer, customer ID, customer name, and then we’re joining orders on customers. So customer ID, there were order ID is equal to the last order ID. So running this here is our last order order ID and then the that’s the date associated with it, and then the customer. So this is this is the customer that placed the last order in our database. Now this in general, is equivalent to if I took this query, so let’s backtrack this just a little bit. So I’ll take that sub query and delete my variable. And then instead of having my variable there, I’m going to say Where oh dot order ID equals and then our sub query. And so running this is equivalent to what I had before. So like I mentioned, we can have, we can utilize a sub query anywhere, we are expecting that type of return value. So in this case, we’re expecting a single value.

And so I can utilize this in the right hand side of our work our our equality operator operation in our WHERE clause. So we can also utilize this as part of a select. So I’m going to initially showcase this as just a variable. So this type of query we’ve already seen before with the customer ID. But now that that customer ID, I’m actually going to tack into this sub query here. So if I actually, let’s take this out real quick, and place that so this is our sub query. And if I execute this, we get 14. But now, if I replace this, and use the result of that as a sub query, we can actually pull the customer information for that particular order. So this is relatively, this is a little bit more difficult or not, I shouldn’t say difficult, but a little bit more difficult to follow, logically. So from sales customers, where customer ID is equal to this customer, right, so 1058. So that’s what we get there. Select customer ID, customer name, and then the order count. We’re only counting orders for that specific customer ID. This is equivalent to, well, we could do this in a lot of different ways, right, we could actually do a grouping we can do, we can specify, or we can do an inner join here. So we can do sales, orders, oh, enter join sale, customer see, and then group and then do account.

So there are a lot of different ways that we can actually write this kind of query. But what happens if there are no results and a scalar valued sub query. So let’s go ahead and put in the full example. Sorry, here we go. And then I’m just going to execute this little bit of SQL here. So as you see, we have customer name. But there’s nothing as a result of it right. So there’s no rows that are returned from the sub query. And so if I use that, down here, I actually get no as a result, because customer name actually does have a column, but the column has no rows associated with it. So this is actually more. So this may look kind of odd. But it can be useful in certain situations. We can also just as we have before, we can embed this entire query here, in place of this variable. This even looks more odd. But it executes just the same. So these are just a couple of examples where we might use a self contained single value or scalar subquery. And the following examples are going to take a look at self contained multivalued sub queries so self contained sub queries that return a column with more than one row

Multi-value Self-contained Subqueries

Video Transcription

Welcome back everyone. In this video, we’re going to be taking another look at self contained sub queries. But more specifically, self contained sub queries that return more than one row as a result with one column. So self contained sub queries, remember is a query that can execute in isolation, apart from our outer query. But a multivalued self contained sub query is going to be a sub query that returns one single column, but potentially more than one row. So these types of queries can be utilized with predicates like N, or also things like exists as well, which we’ll take a look at. But syntax looks something like this. So the scalar expression, so it could be a column, or a single value in and then the result of the sub query. Most of the time, this type of sub query, though, can be rewritten using joins, and I’ll show a few examples of this. Okay, so let’s take a look at a self contained multivalued sub query. So in this example, I have a another variable up here, that is just specifying a specific date. And then in my sub query down here, I have a select for customer ID customer name from customers, where the customer ID equals and then this result here. So let’s go ahead and execute our sub query. Cheat for just a second, because I need my variable declared appear.

So temporarily, I’m just going to highlight this and run that. There we go. So for this particular order, there are no customers are sorry, for this particular date, there are no customers. So if I backtrack this, and then run this again, I get no results, no results. Now, and this is contrary to the previous video, where we looked at a single valued sub query that returned nothing and was utilized in a single value expression. There our end, our outer query actually had a no value for a row. Here, I actually have nothing. And so let’s go ahead and change this to a another date. And give this a run. Ooh, okay. So there we go. So sub query returned more than one value. So n equals, if I step out of the way here, equals naught equals less than less than or equal to greater than, greater than or equal to is not allowed for a sub query. That is that returns essentially more than one row. So if I am going to take this query back out. And if I highlight this, and executes, hmm, so you see, now I have a multiple roses original 55, to be exact. So I can actually execute this utilizing a single equals two, because this is a single value operation value on the left equals value on the right, but the value on the right here has more than one row. And so Microsoft, the T SQL, the SQL doesn’t actually know how to do that comparison. So instead of using the equals operation, we’re going to use the in comparator. So instead of using the equality operator, we’re going to use the in operator. So where see customer ID is in this. So let’s execute.

Now, I actually have all of my results associated with so I do have 55 customers on that order date. But here, I actually have only 46. This is because there are a customer down here could actually appear more than once because this is from the Orders table. So this is all the customers all of the unique customers that have placed an order on May 5 May, May 30 2015. Let’s take a look at another example here. Oh well first, let’s Let’s change this back to 531 and see what the results are. So, go back to this, we actually still get nothing because there are no customers and nothing right. So this is an empty set. And so when we compare when we check to see if a customer ID is inside of that empty set, nothing actually gets returned, because, again, nothing matches that nothing is nothing. But I guess it is important to note here that we still don’t get a no row as a result here. So let’s take a look at another example, where we have a self contained multivalued sub query. So here is a an example where we have this big inner query, I’ll highlight this and run this so you can see what it returns. So bunch of salespersons IDs, and you notice there are duplicates here. So select salesperson ID from and then we have a big join stock items, joined with order lines and orders, where the stock item is this particular item, USB food flash drive chocolate bar. So that is then fed into the outer query. So select Person ID full name application, where Person ID is in this result.

So in this case, what I’m pulling out is all of the individuals where the where the that particular salesperson made a sale that contained this particular item. But let’s take a look at real quick on how we might rewrite a couple of these, let’s go ahead, I’m gonna go and backtrack here, just a little bit to the previous one that we did. So remember, customer ID customer name, that placed an order on this particular date. So I just want to show an example that we can actually rewrite this to be a join instead of a sub query. So I’m going to actually move this WHERE clause, cut that real quick. And then from the sub query, we just want the, we want the orders information, right, because we want all of the customers who placed an order on a particular date. And so we can do an inner join here. And this could also be done with a cross join, but with additional WHERE clause. So inner join, and then we have C, oops, sorry, sales dot orders. On and let’s go ahead and go down to another line here.

So I want to enter join C dot customer ID equal equals, do let’s give this an alias, O. O dot customer ID. Now I could do this one of two ways, I could place my date here in my WHERE clause and give us a run. So this gives me the exact same result as I had here just to kind of showcase these two different queries here. So if I run both of these at the same time, you can actually now here, I have 55 rows, and here I have 46. So remember with how this is going, right? Every customer ID inside customers is unique. But every customer ID and orders is not necessarily unique. And so now up here, if I just add a sorry, if I add this state qualifier and run this and look at my rows. See, there we go. So now we have two identical queries, one that uses a join and one that uses a sub query. Now there’s not any inherent performance boost between either of these, logically, roughly they are identical in nature. But one just uses the join and one uses a sub query in its place. But again, we’ll be exploring more sub queries and following videos. And in some scenarios, sub queries that are not able to be rewritten as joins or sub queries that would be more complicated to be rewritten as joins.

Correlated Subqueries

Video Transcription

Welcome back everyone. And in this video, we’re going to continue our discussion on sub queries. So previously, we learned that we could nest one query inside of another. And that led us to having outer queries and inner queries or sub queries, and the those results of the inner query then being passed back for processing and the outer query. And we also learned that with sub queries, we have two primary variations, self contained and correlated sub queries. With each of those having three potential types of returns single values, multiple values with one column or table valued, meaning that we have more than one column with zero more rows. Table valued sub queries are going to be reserved for another video. But what’s the difference between self contained and correlated sub queries? Well, remember that self contained sub queries are able to be executed in isolation, meaning that there’s no dependency between the outer query or the inner query with the outer query. Logically, self contained queries are executed only once, and the result is then passed back to the outer query for use. correlated sub queries, on the other hand, do have a dependency on the outer query. So the inner query here in case of a correlated sub query is going to have some reference to the outer query.

Usually, this is in form of an attribute or column reference to a table in the outer query. Logically, correlated sub queries are executed on a per row basis from the outer query. So for each of the rows inside of the outer query, the inner query gets executed. So correlated sub queries can be used for both scalar and multivalued queries. We learned last time though there are some quirks with self contained sub queries, and the differences between a single valued sub query or a single valued sub query and a multivalued sub query. With correlated queries. These are used in a variety of contexts. And they’re a little bit more difficult to rewrite when compared to a self contained sub query, for the most part anyways. So we can use this to usually do things like running aggregates versus the common use. And another benefit here is that it can be used in both the Select and where clauses. The common way to replace a correlated multivalued sub query is usually using the where exists clause, which uses two valued logic, unlike the in operator, remember, with Boolean values in SQL, we have that weirdness where we have true false and undefined. Well, the exists is yes or no, right? It does exist or doesn’t exist, exist operates on a slightly different context. It’s either it either exists, or it doesn’t. Unlike in where we have a possibility of, yes, it’s in this subset. No, it’s not in this subset, or I don’t know.

So, particularly in in situations where we’re dealing with no, but I think it’s a little bit more straightforward to showcase some examples. So let’s start taking a look at some correlated sub queries. So the query that I have up here on the screen was the last one that we covered in the previous video, which was a rewrite of a self contained sub query. So remember, we have the self contained sub query here, that pulls out the customer ID for for the orders were the order is on May 30 2015. And if I execute that, right there, we get all the customers who placed an order on that one particular day, and then we use the in clause to to incorporate that result back into our outer query. I could rewrite that though, using just an inner join, and then using the distinct qualifier, so we take out any duplicate customers. Now this is in contrast to a correlated sub query. Note with my with my sub query down here at the self contained, I have no reference to anything out in the outside in the outer query. My sub query as I did just a little bit ago, can be executed in itself by itself without any information from the outer query. But if I bring in a correlated sub query like this. So similar tasks that we tried to do before, similar to the first query we covered when we started talking about self contained sub queries. But here inside, I have the sub query where we have a select Max order ID, from sales orders where C O dot customer ID equals O dot customer ID.

So this is the trick, right? So this oh, well, where to table Oh, come from? Well, that’s all the way up here, and the outer query. So in our outer query, we have from sales dot orders, oh, where oh dot order ID equals the max order ID, where the customers actually match. So this is, you know, think of this as kind of like a join, right, we are joining the outer table, the outer query with the inner query using this predicate, essentially. So the order ID from the or the customer ID from the rows on the outer query are being fed into this inner query. And since this inner query here is executed, after our FROM clause, we actually have access to the tables that are available to us in the from clause in the outer query. So let’s go ahead and give this a run and see what happens. There we go. So logically, right, what happens is that my outer query starts first, of course, so from clause and our outer query, then our WHERE clause and the outer query executes. But the right hand side evaluation is done every single for every single row in this table here. Alright, so for every single row that is produced from the front clause in the outer query, the sub query, the correlated sub query in the where clause is executed for each time, correlated sub queries, in general, are more expensive to run, because of this fact, because they are ran against every single row, and in the Related sub and the related outer query that they’re associated with.

So do be careful when you’re executing this. But essentially, what we’re what we have here is, I am retrieving the last order for every customer. So what was the last order, so the biggest so order IDs are from smallest to the larger the order, the later the order was actually made. So this gives me the last order for every customer. Now let’s take a look at another example. But in this, but this time, we’re going to take a look at a correlated sub query that’s in our select clause instead of our WHERE clause. So this query is identical to the one that we previously executed, so we get the similar results. So let’s actually pull this one out. So just so we can see the differences here. So remember, our previous one, so customer ID, order ID, order date, salesperson, and this time, and the previous one, we actually attached the salesperson, and this one we are doing similar issue, similar, similar idea. But now we’re just doing customer ID, customer name. And last order ID similar, not quite identical. But up here we have the order date. So let’s take this back out, and go back to just this one here. So getting all the customers along with their order ID of their last order. But right now, we’re not filtering in the where clause, our last order is being pulled out of this correlated sub query. So select Max, order ID, from sales orders, where oh, customer ID, which is the inner query, equals see customer ID, which is the outer query. So I’m not actually using this to filter any rows out from the outer query, I’m just actually adding new information in that I don’t have any outer query.

So this is another common example that you may see using a correlated sub query. But let’s work on a new example here. So what if I wanted to show all customers with the number of orders placed in a particular year? And this sense we want customer ID customer name, and then we also want a new column called In order to account for that particular year, so I’m going to pick on 2015 here. So I’m going to go ahead and type this out. See? So we wanted Yeah, let me skip down here to the from clause real quick. So we have our table sales dot, customer, there’s Steve customer see. And then I want to see customer name. And then let’s also do C dot customer ID. And then we’re going to have a sub query here. And I’m going to put parentheses there for now. And I’m going to do as last. Sorry, let’s do 2015 Order count. And it’s probably going to yell at me for having a number there. So let’s put this in square brackets. There we go. Okay, so um, I also want to order by that as well. So make things a little bit easier to read order by, and then 2015. Order account, there we go. Okay, so how do I get off all of the customers with orders placed in 2015 and count the number of orders. So I only want customers that were in 25th, that placed an order in 2015. Now, of course, I could do a join here. But let’s do this with a sub query. So we have select, and now we’re trying to get the order count. So of course, we need to start with counts, right count star, I want to count all of the rows.

So let’s pull this down. So it’s a little bit easier to read. There we go. Okay, we want where do we get all of the orders. So that’s sales. Dot orders. There we go. Let’s go orders orders, oh, and then we have a where clause here that we need. So we want all of the orders that are between 2015, January, one, one and December 31. And we all but we first need to also we also need to consider linking the inner query to the outer query. So let’s go ahead and do the order date first, because that one is straightforward. ODOT order date is between and then we want only those in 2015. So 2015 Dash 101 Dash o one, and 25th teen three, one. Okay. So this gives me, let’s Well, let’s go ahead and give this a quick run. So what does this query give me as it is right here. So this gives me all of the how many orders that were between one 120 15, and 1231 2015. So all of the orders in 2015. So there’s a total of 23,329 orders in 2015. But we only want the count for that particular customer. Because if I ran this as it is that same value, this is a self contained sub query. And so that value is that sub query is executed once and is copied across all of the rows.

But what if we wanted to do just one individual customer? Well, we just add an and clause to our were here and link our inner query Oh, customer ID to the outer query see customer ID. If we run this now, of course, it is now correlated. So our correlated sub query has no knowledge. If I ran it in itself, right? We get an exception, right? Because it has, it depends on sales customer see, so this table here. And so since it has no knowledge of that, we have to run this run both of them together. But that’s the primary difference between self contained and correlated. But let’s give this a run. And ooh, now we got but we got some got some, some people who didn’t place any orders in 2015. And then we got some people that placed a few and then a bunch of people that place a lot of orders. So if we actually sort this descending, where you can see who had the most. So the top two were the tail spins and spins. And that’s just a quick walk through example of how we might complete that query. So again, this is all customers with the number of order or their with their numbers of orders placed in 2015. So I’ll have a few more examples of this for you to try on your own. But for now, that will stop for this video. And in our next video, we’ll talk about more correlated sub queries, but specifically multivalued correlated sub queries

Multi-value Correlated Subqueries Part 1

Video Transcription

Welcome back, everyone. So let’s continue our discussion on correlated sub queries. So previously, I showed some examples of a correlated sub queries primarily, though with scalar value return, so correlated subqueries, that only had one value as a result. However, the distinction between that and a self contained valued a self contained single valued sub query is that the correlated version has to be executed for every single row four, the outer query, versus the self contained query is only executed once. But without further ado, let’s take a look at some more examples of this. So usually queries solved with multivalued sub queries takes one of two forms. So we’ve already seen the first form which is shown here on the screen. So this is the self contained form, where we have in this case, give me all of the customers who have placed an order between or in 2015. So in, and so this is, these are all the customers and 25th, the all the orders in 2015. So if I run that, there’s all of our customers who placed orders in 2015. And then to get their customer name, I linked that to the outer query. Or I embed that in a sub query and link and have the outer query pulled the customer name, as I talked about last time, write the self contained version here is easily rewritten using a join as we shown before, but we can do a another version of this using the exists predicate.

And so let’s go ahead and see the difference there. So if you see here, right, self contained, I have no linkage to the outer query here. But in this query, very similar, very similar. But now instead of in I have exists, and I don’t actually have a check here for a column, right? So where before I had, where customer ID in, but now I just have where exists, okay, but that customer ID is now checked in the where clause in the inner sub query. So or sorry, in the sub query. So I have Oh, customer ID equal c dot customer ID, C being the outer reference. And order date is between, you know, 20 in 2015. So, both of these queries are 100% identical, as far as output goes, logically, they’re just executed a little bit different. Both of these, of course, I can actually rewrite using a join. So this query, and this query here, right, I can rewrite using joins. So inner join between customers and orders on customer ID. Remember that Oh, customer ID equals see customer ID that was my correlated sub query before, where order date is between one 120 15 and 1231 2015. But simple example, just showing you an easier one that can be rewritten fairly easily. But again, logically, all three of these queries are have the same result. Each one is just executed just a little bit different.

How did we see all customers who did not place an order in 2015? Well, remember the remember what we did before is that instead of an enter, join, we did a left join. Right. So from sales customers see left join on orders. Oh, customer ID equals customer ID. And we also have, we also want to actually put this I’m going to move this up one. I’m going to add this my predicate for my join. And so similar same query as I had before, I just have a left join now, and I’m replacing my WHERE clause here to be. Oh dot order ID is No. So this is what we did previously to find all of the customers who did not place an order and 2015. So how do I use a sub query to get all of the customers who did not place an order in 2015? Well, an easy way to do that is just to include the knot here. So where not exists. So records that don’t return a row here will not be included or will be included. Whereas exists rows, when the sub query does return a record, then that is included as a result. So differences between where exists and where not exists. The primary primary difference here, right is that the exist predicate uses to value Boolean logic, right? True, true to Boolean valued logic. So true or false, right, there is no undefined. Unlike the in operator which has, which is three valued Boolean logic, true, false and undefined. So that’s one big difference here that we have to consider using these operators, these predicates, they exist is only two valued two valued Boolean logic.

But let’s take a look at some more examples here. So do I have this query here, this is a pulling from a table that we haven’t pulled from before delivery methods not used by suppliers. So delivery methods not used by suppliers. So all I have here is a left join between delivery methods and supplier. So this gives me all of the delivery methods. And then I want to pull only the records where supplier ID is null. So the ones that did not match. So let’s go ahead and give this a run. So I have five delivery methods that were not used by supplier, so chill band, customer collect customer, Curie delivered van and post. So let’s take a look at a couple other potential ways that we can answer the question for, you know, finding delivery methods that are not used by suppliers, delivery methods that are not used. So at face value, this particular query looks like it may potentially work. But if we execute this, we find that the results are empty, right, we actually have nothing as a result, right, we have nothing as a result. So delivery method, if we just actually highlight this, around that. So here are all of our delivery methods here. So we have 10 different delivery methods.

And then if we run this query here, here are all of the delivery method IDs. So n starts to operate a little bit weird in this situation, right? Because two is definitely in this right, in this result, because we have deliver method ID here. But this query is not going to end up working. And some of this has to deal with issues with how no works. And remember, n is going to be a three valued Boolean logic here. So we can kind of get some weird results when our sub query returns any null values. So let’s take a look, stick another attempt at this. But instead of doing a, a, not N, let’s change this to do exists. Let’s execute this query here real quick. And there we go. We actually get the correct results here. So the delivery methods that are not used by a supplier so what essentially we’re doing here is very similar in spirit, the same idea here, but we’re not quite leveraging that relationship between the two. That’s the big reason here we’re not leveraging the relationship between the two. So select the delivery delivery method where not exist select star from purchasing dot suppliers S where S delivery method ID equals DM delivery method ID so here is our correlation between the inner query and the outer query. So if there is no supplier For the delivery method that I’m for the delivery method from the outer query, then exclude that row and then exclude that row. But with the correlated sub query, we can actually leverage the relationship between the two tables, instead of trying to use the set operation not in but I’ll stop this video there, because this video is running a little bit long. But in the following video, we’ll take a look at a couple more more complicated examples of correlated sub queries.

Multivalue Correlated Subqueries Part 2

Video Transcription

Welcome back everyone. Let’s continue our discussion on correlated sub queries. So previously, we showed some different examples on how we can rewrite different sub queries with different operations like left join, we compared the differences between or the results of the in operator in the exists operator. But let’s take a look at a deeper example. Or a larger example of a correlated sub query, or at least another common use. It’s one of the common uses that I listed, we’re running totals and for a variety of calculations like that. So this is one variation of that use case. So most of the most of this query is something that we actually have done before, it appears more complicated than it really is. So let’s first start out by explaining this query using the from clause since that’s where we initially start out with our processing. So from sales orders, enter, join on sales order lines, where order date is between in 2015. And then we group group by order ID and dates, order by the dates, and percentage daily total. The columns that we pull out here are order date, order, order ID, the order total, which we have calculated before in a previous video. But the big addition here is this sub query here.

So this is the new part of the query. So what I’m calculating here is the percentage of sales that this order contributes to for that for a particular date. So for the date that this order actually happened on how much money of the of that total, total tally, did it contribute? So 100 times some, so 100 times the order price divided by So some and I use RT as an abbreviation for return. So some of the return order lines quantity, so this is the, again, calculating the order total, from sales orders return Oh, so our to enter join order lines, again, different alias return order lines. And so again, this is still within our inner sub query, our our sub query here, but here is the our link to the outside our to return order date equals ODOT, order date, so give me so what this sub query is doing is calculating the total amount of sales that happened on this particular order date. So for this particular date, what was the total amount made, and we use that to divide into the order, the order total for that one single order, and we get that percentage. So again, a little bit, it looks a lot more complicated than it actually is most this we’ve seen before. And again, really, this inner query is calculating the total, the total amount made for that particular day from the outer query, and then using that in the outer query to calculate the percent of the daily total.

So let’s do let’s review one more example like this. So again, a running total is a another general use case for a correlated sub query. So let’s paste this one in here. Another example of how a correlated sub query can be used. So this time we have up let’s go and run this again. This time, we have a lot of the customer information being pulled in here, but we have order date order total, and then you’re YTD year to date running total for customer. This one is a little bit more complicated than what we had before. So we have a two joins here, customers on orders, orders on order lines, we’ve done this join before. So we can link the customers customer to the order that they made, grouped by the customer and then grouped by the customer first, then grouped by the order. So all the order for that particular customer are grouped together for each order. And then we select the customer information, order order dates, the order total.

So that’s all something that we We’ve covered before, but here is our new addition, our correlated sub query. So for this correlated sub query, we’re calculating the order total. From orders, inner join on order lines, where the customer ID from the inner table, right, the inner R T, O, the inner order table matches the customer ID of the customer table in the outer query. And the order ID matches the order ID in the outer query and the yours match yours match. And so if we assume that the order IDs are consecutive, that means the total running here is true, right year to date running total for the customer. So this is the first order that customer one place this is the total sum of 690 plus 3636, which gives us 3756, and so on, and so forth. So this is a really neat way to do a running total, just like what you would potentially do in like an Excel document. But we’re achieving this all with SQL with a correlated sub query. So this is probably one of the more common complicated uses of a correlated sub query. But again, as we showed earlier, the simpler use uses of a correlated sub query. Most of the time can be rewritten using just joints. But that will conclude all of the examples that I’ll talk about in videos for sub queries. If you do have questions, please reach out and we’ll be happy to help