Now that you know how to run basic queries. Let's talk about how we can start to filter down the logic in the results that in the queries themselves. So right now when I have a query like this that says

Select * 
FROM users;

If I run this it's just kind of a dumb query. I mean there's not really any logic going on here, it's just me telling me to bring back all of the records in the Users table. Rarely is that something that you're going to want to do. Usually, you want to have some type of query criteria and that is where the “where” syntax comes into play.

So let's now create a little bit of a different type query that leverages “where”. Here I'm going to say SELECT all FROM users WHERE users_email is equal to Christine@test.com; . Now if I highlight all of this now and if you look down on the result you can see that it brings only a single email back. It's now not bringing back the entire thing. Now it is saying I'm only going to bring back the users where the email is equal to Christine@test.com. So this is a cleaner way of being able to grab values. Now this by definition still brings back a collection.

We have the ability to just pass a single parameter here called “where” and now let's see how we can stretch this out a little bit differently. So let's say that we want to say where email address is equal to Christine@test.com “OR” the users_email is Jordan@test.com. So now if I run just this code right here, now you can see that this brings back two records.

It brought back the one that matched for Christine and the one that matched for Jordan. So this is a way where we can extend and give a little bit more flexibility to what we're trying to build.

I have a script that runs on for a legacy application that has some data corruption issues but the application itself can be changed but since the client doesn't want to pay to have it changed. So every once in a while I have to run a script that goes and updates a number of user accounts. Now we haven't gotten updates yet but the way that I can pick out which accounts it is by using this type of syntax “where”.

I say “I want you to go and find the users” then I have all of the users_id and I just create a script that creates those for me and it goes through and it says I want to make that change for this, this, and this user. So this is a nice way of being able to grab those elements and we're not limited to a single one.

You're going to find out that the “WHERE” clause is incredibly powerful and it's something you're going to use on a very regular basis. As we go through and talk about deleting and updating the WHERE clause is going to be something that you do quite a bit.

Now just for practice we'll run through a few more. So now let's play around with it. I want to SELECT the CITY and the STATE where the state is equal to New York. You also have to declare the table. So now I say FROM addresses. There you go. That's where the syntax highlighting is very helpful.

Before I even tried to run a command that would have broken it told me that I was forgetting one item. So here I'm saying I want you to bring me back the city and the state from the addresses table where the address state is New York. So now if I run this this is going to run.

You can see it brings back three records. It's brings back two in Manhattan and one in Queens. I definitely recommend for you to play around with not just these commands and I'll put these in the show notes but also play around with commands that you make up on your own. Go through the full list of attributes in the columns that you have, run different queries, and see how you can explore those after this guide is over.

Now one other item I want to add to the “WHERE” clause that is very important is “AND”. Right now we have “OR” but I really want to see the difference. “Or” allows us to have multiple values that allows us to create some flexibility with our query, where we can tack on different parameters that will make a query bring back positive results.

That kind of opens up the scope on what gets returned. There are also times where you want to limit the scope. Where you want to say “I want you to bring me back items that fit multiple criteria points” and that is where “AND” comes into play.

Copy all of this and coming down I am going to paste this in.

You know to this end and I'm going to get rid of a few of these items because I don't think I actually want to do the users, I want to go with addresses. I’m going say select all from “addresses”. Here will say where the address and we'll go with addresses_state is equal to New York just like we had in the last query. Get rid of “OR” put “AND”. Change users_email to addresses_city and set the city equal to “Manhattan”. Now if I highlight this query and run it. You can see that this brings back two addresses. This is pretty cool.

So what we're able to do is actually create a filter that limited the number of items that came in by adding additional criteria. The biggest way or the easiest way of understanding the difference between “OR” and “AND” is or opens up your query to extend it. It allows you to have multiple things that could be true.

In other words with this query we're reused “OR” this is going to bring back the records where the email is equal to “kristine@test.com” or it's equal to “jordan@test.com”.

So that could have multiple items that could meet those kind of criteria. Here, this is more limiting and we're saying that we only want the records that have a state of New York and the city of Manhattan. What that means is that it is going to find all the ones that have the state of New York.

But we’re going to tack on this extra query and this extra requirement. It’s going to say I want all of the ones that have New York as a state but I also only want the resultset limited to Manhattan. This is something that is very helpful.

One other thing that you may know is that you can tack on as many of these as you would like. So I could copy this and say instead of addresses city I could say users_id. I can say and addresses_user's_id is equal to one.

Notice that this is going to bring back a single resultset. This is also very helpful and that we can take down and filter our list as specifically as we want and it just by using these “AND”s. Additionally, we could use as many “OR”s as we want. If we want to extend the query out to grab more values. Now if I run this query it runs and it brings back only a single address, which is exactly what we want because that is fitting the criteria we had.

So in review what we've done in this guide is we've walked through the WHERE clause. We also walked through how we can limit a result set based on a certain search criteria. Then we also found how we can extend it either to make it more flexible using “OR” or we can limit it and make the query even more specific with the "AND" command.

Lesson Code

USE devcamp_sql_course_schema;

SELECT *
FROM users;

SELECT *
FROM users
WHERE users_email = 'kristine@test.com'
OR users_email = 'jordan@test.com';

SELECT addresses_city, addresses_state
FROM addresses
WHERE addresses_state = 'NY';

SELECT *
FROM addresses
WHERE addresses_state = 'NY'
AND addresses_city = 'Manhattan'
AND addresses_users_id = 1;