Non-aggregate subqueries - SQL Tutorial

- [Instructor] A subquery does not always contain an aggregate function as we've been showing previously. If we take a look at the following SQL statement, we will see that the query shows the transaction date for a specific transaction, and that date is January 9th of 2012. Now if we wanted to see if there were any other invoices that were received after the invoice date reference here, we would build a subquery wrapped in a pair of parenthesis and then build an outer query around it. Let's go ahead and do that. To make this a subquery or the inner part of the query, let's go ahead and put some parentheses at the start and all the way at the end of this. Now let's go ahead and build out the rest of our query. I'm going to start above here, and let's go ahead and say from, and we'll say invoice, and let's include what we want to select here. Let's select a few fields from the invoice table. So going above the from and putting our select, let's include the fields invoice date. Let's also include billing address, and let's include the billing city. Okay, we have our three fields included. Now, let's say what we want to filter down to. And if we recall, we would like to get all of the invoices that occurred after this particular invoice date. So now we want to say where, and we'll say the invoice date is after meaning greater than, so we put our greater than sign, this particular value, which we know is January 9th, 2012, but we've surrounded our subquery with open and closed parentheses. And now let's go ahead and run our query. Before doing so, just taking a look at the full thing, all we've done is wrapped our original query which gave us our date of January 9th, 2012 in a pair of open and closed parenthesis. Then we built out our outer query to include the where clause, and we are saying where the invoice date is greater than this particular value, which we already know. Now let's run our query. Now, we've effectively responded to the question of, give us all the invoices that have occurred after this particular date, and we've done so by including a subquery in our where clause and pointing out that this subquery is not aggregated in any way, but it has been effectively used to respond to this particular request.