IN clause subquery - SQL Tutorial

- [Instructor] Up to this point, we've only used subqueries to calculate a singular value, which is then passed to the outer query. It is possible to use subqueries that return multiple records. Now, let's say that our WSDA Music Management team is interested in three particular invoices. To select these individual invoices, let's consider our present SQL statement here. We've used a SQL statement that is going to employ the IN operator, or the IN clause, to select three specific invoices, and our result is showing the invoice date that is associated with these three invoices. Now, let's say that we are asked if any purchases were made on these particular days. If we want to select all invoices for those three days, we can either start a brand new query, or we can just use our existing query here as a subquery. Let's go ahead and do that. Now, to create a subquery, as we said, let's put our existing query in a pair of parenthesis. Open at the start, and close at the end. With this step taken, let's now start to build out our outer query. Let's start with the FROM, and we want to say from the invoice table, and let's select a few fields. Let's go above that FROM, and let's select the invoice date. Let's also select the billing address. And last but not least, let's select the billing city. Now we need to just include a WHERE clause, to say where the invoices are matching the invoices that we've already selected from our previous statement, which we've now wrapped in a pair of open and closed parentheses, thereby putting it into the inner part of this subquery. Now, let's include our WHERE clause, and we want to say WHERE, the invoice date, and we want to now include, yet another IN clause. So we're going to say invoice date, IN. And now, we're just simply going to include our subquery here. Now, we know that this particular subquery, or now that it's the inner part of our subquery, is producing three records. So it is producing multiple rows of records. Because we are anticipating multiple rows in our outer query, we've now equipped it with a WHERE clause, as well as the IN operator, which as we know, can handle multiple records. So let's now go ahead and run our query. Now here, we have completely responded to the request, and we're able to supply WSDA Music Management with the result of this query. And it now responds effectively to them, telling them if any other purchases were made on those three original dates. Now, this technique of turning an existing query into a subquery, is pretty useful when you're dialing in on your data. This method actually allows you to reuse an existing query, and modify it further to narrow down your search, as we have done here. Great job.