SELECT clause subquery - SQL Tutorial

- [Instructor] To demonstrate the use of subqueries, let's begin with a simple SELECT statement. This is the same SELECT statement we used in our last chapter where we found the average invoice total in the invoice table. We can see that the average invoice value is $8.06. Now, let's say that we were asked by WSDA Music Management to gather data about all invoices that were lower than this average. Well, below here, I've started a partially constructed SQL statement that is going to attempt to respond to this question. Now, we would need a SELECT statement that displays some of the invoice fields such as invoice date, billing address, billing city, and, of course, we need the total. We would then want to filter our results by comparing them to an aggregate function. We would want a WHERE clause that compares the total to the average total. Now, if you recall in our last chapter, we did learn that attempting a direct comparison within the WHERE clause would generate an error. In fact, if we attempted to put our aggregate average in the WHERE clause, we saw that we got a misuse of aggregate function error. So we need to find a way to take our original query, the one that gave us our average total of $8.06, and insert all of it inside of this new query that we're attempting to construct. Now, fortunately, in SQL, there is an easy way to accomplish this and it is by use of what's referred to as a subquery. Now, let's continue to build out our statement here and include a subquery. So the last piece we need to include here is our filtering and we want to say where the total is less than the average total amount. To do this, let's first start by including a pair of open and close parentheses in our WHERE clause. Now, we're just simply going to repeat the statement or the SQL query that has produced this particular result. And if you recall, this is our query. So in other words, we're simply going to include this query inside of this one. Let's do this. We're going to say select and we want the average total and this is going to be from our invoice table. Now, by doing this, we're saying we want the average total from the invoice table. In other words, the exact same result from our original statement. In fact, I'm going to just highlight this portion of our query and rerun this. Without the round, we still have our $8.06. Now, if I run our entire query here, let's observe the result. Now we have our query producing exactly what we want. We have the results displaying all of the invoice information where that invoice total is less than the average amount, which we know to be $8.06. If we take a look at all of our totals here, it is actually less than that $8.06. We have sorted in descending order by our total, which means the highest value appears first, and it does indeed confirm that this is below the $8.06 So this full query is what we needed to respond effectively to the latest request by WSDA Management. And we can respond and say, "Here are all of the invoices that are less than the average amount." Now let's just take a look at a diagram that's going to reinforce the components of the subquery so that we are fully understanding how it's laid out. Now, here we have our subquery and various parts of it that it's referred to as. Now, the inner part of the subquery is simply the part that is between the parentheses. So when we created our WHERE clause and then said total less than and these open and close parentheses, this particular area as indicated here is referred to as the inner query. And again, that's simply the part that's inside of our parentheses. Every other part of the query, and that is this area as well as this area, is referred to as the outer query. So in other words, the original query that we started off with is referred to as the outer query and the part that goes inside of the parentheses is referred to as the inner query.