So we've been using the guides table quite a bit lately. I want to switch it up and let's go take a look at the addresses table. I’m going to say SELECT * FROM addresses just to see exactly what we have here.

One other thing if you are following along and you're using MySQL workbench is instead of having to go up and hit the lightning bolt every single time to run it, after the code is highlighted you can just hit command + return to run the code.

This is going to do the exact same thing as hitting the lightning bolt just to make things a little bit more streamlined as you're going about your queries. When I run SELECT * FROM addresses this is going to bring back each one of the addresses.

You can see we have cities from Manhattan, Phoenix, and Queens. So what do we need to do If we want to find multiple cities? There are a few ways of going about it and you may already have a good idea of how to do this. There is a better way that I can tell you.

This is something we've covered before where we can say something like where addresses_city = “Queens” OR addresses_city = “Manhattan”. Now if I select all this and I run it.

This is going to bring back all of the records in the addresses table that are for Manhattan or Queens. This is perfectly fine, however, imagine a scenario where you actually have hundreds of cities that you want to find. This would be a very long nasty looking query and it just would be a poor way of doing it especially if your goal is to be more professional.

When you're building out your queries it's a good idea to find more efficient ways of doing it. There is a more efficient way and I'm going to be able to get rid of OR addresses_city=”Manhattan”. The way we can do it is, I can do a “WHERE IN” clause. Here it's going to say SELECT * FROM addresses WHERE addresses_city IN (‘Queen’, ‘Manhattan’) Then I can say instead of equals I can then pass “IN” parentheses the cities that I'm looking for.

Now if I run this code you're going to see this brings back the exact same result set. So this is something that's pretty cool. I think this is a much more efficient way of being able to run large queries that have the exact same parameters. So if you ever run into a situation where your where clause has the same column name and you're looking for multiple items inside of it. Then using the WHERE IN version is much better than just having a dozen of clauses and you're going to end up with the exact same results.

Code

USE devcamp_sql_course_schema;

SELECT *
FROM addresses
WHERE addresses_city = 'Queens'
OR addresses_city = 'Manhattan';

-- Is the same as:

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