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.