In the last guide, we walk through how to build a subquery and we work through a pretty basic example. In this guide, I want to extend that out and show you how the subquery can allow you to work with dynamic data and even with collections of data. So here let's take a look at our addresses. I mean to say SELECT * FROM addresses and now if I run this code right here you can see it brings up our full database of addresses.

Most of them are in New York and we have one in Arizona now. Now a very cool query to build is one that involves the “win constraint”. What I can do is I can actually generate a subquery and pass in the values into a “WHERE IN” statement and then have it return the result. In other words, what I'm going to do is I'm going to keep this SELECT * FROM addresses.

Then I'm going to create a “WHERE IN” statement that says I want you to find and return all of the records that have a city that's in New York. The subquery is going to go through and it's going to return all of the cities that it finds that have a New York address and then it's going to return that full set. Here what I can do is I can say WHERE addresses_city IN.

Notice I'm using the same exact syntax as before when we are hard coding the values but we're not going to hard code it. Now we're going to open up our parentheses and inside of this I can say “SELECT addresses_city FROM addresses WHERE addresses_state = “New York”. Then I'm going to close off the parentheses and let's run this and see if it works.

What we're doing right here is pretty intuitive as we're able to actually create a query inside of it. We don't have to run two separate ones where we grab the values of all of the cities that are in New York. Then pass it into a second query instead. We're able to just pass that subquery right into the statement.

So if you remember how the IN statement worked. The way the statement IN works is we had to pick out the cities we wanted so we can say Manhattan and Queens.

Now if I run this is going to return the same result set.

Notice that we had to hard code these values in. There are a couple of problems with this approach. One is let's imagine that we're building a Yellow Pages application or a way to query it and there are 500 cities Inside of the query. That would take a very long time to search for.

Instead, we can just say in my subquery bring me back all the cities. The other issue with this approach is you may not know the cities. That can also be very problematic because here you're having to hard code them in. You’ll have to know where they are coming from and what the values are.

This is why I wanted to dedicate two guides to subqueries because I felt like it was very important to be able to understand not only the syntax and the commands. I also want you to understand the importance of it because if you were building out an application like this. Where you have a list of addresses and you need the ability to comb through all of the addresses that were inside of a certain state. If you have some secondary parameters then that is going to be a nice way of doing it.

The first thing I would ask is you know well why wouldn't I just do a SELECT * FROM addresses WHERE addresses_state = New York you'd get the same exact result. So in a real life scenario, the reason to do this is not for a single subquery. It is also to extend it out and to have even more items.

You could say SELECT addresses_city from addresses where address state is New York and you could also say, where the addresses_user_id is something else. You could nest all of that inside and you could wrap that inside of a single query. So by leveraging a nested subquery it really gives you the ability to be much more expressive and have much more flexibility, when it comes to building out your own sequel queries.

Code

USE devcamp_sql_course_schema;

SELECT *
FROM addresses
WHERE addresses_city IN (
  SELECT addresses_city
  FROM addresses
  WHERE addresses_state = 'NY'
);

SELECT *
FROM addresses
WHERE addresses_city IN ('Manhattan', 'Queens');