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