Where Clause in MySQL | Beginner MySQL Series
Hello everybody! In this lesson, we're going to be taking a look at the WHERE clause. The WHERE clause is used to help filter our records or our rows of data, whereas the SELECT statement is used to help filter or select our actual columns. So when we're using the WHERE clause, we're only going to return the rows that fulfill a specific condition.
Let's take a look at exactly how this works. Let's say we come right up here, we're going to say WHERE, and now we need to specify what column we're about to create this condition for. So we're going to say first_name. We're saying WHERE the first name is equal to, and let's do quotes and say "Leslie." So we're saying the first name has to be equal to this value right here, which is Leslie, for Leslie Knope. If we run this, there's only going to be one row that's returned because Leslie is the only Leslie in this entire table.
Now, we just used an equal sign, and that's actually called a comparison operator. There are a few other comparison operators that you can use. Let's take a look at some of these other ones. Let's pull this down right down here and let's actually highlight the SELECT FROM. I'm going to run it with this one right here; it's going to only select everything from the whole table. So we didn't select that WHERE clause.
Let's go right down here and look at this salary field. I'm going to say WHERE the salary is greater than 50,000. Now, one thing I want to note before we actually run this is that right down here we have Tom Haverford, who makes exactly 50,000, and I think there's one more, Jerry Gergich, who also makes exactly 50,000. If we run this, you'll notice that both Tom and Jerry are not in this output, but in the salary field, everything is greater than 50,000. The reason for that is that Tom and Jerry made exactly 50,000. What we're saying right here is WHERE the salary is only greater than. If we want to include Tom and Jerry, we have to say greater than or equal to, and now we'll select 50,000 or above.
Whereas right here, before we were doing just this, it was greater than 50; it didn't include the 50,000. Let's go ahead and include it and run this, and now you'll notice that Tom and Jerry were both included because they had exactly 50,000, and we said greater than or equal to. Now, we could do the exact same thing but with less than, so we have less than 50,000. Now we only have two people who make less than 50,000; that's April and Andy. If we say less than or equal to and we run that, now we include both Tom and Jerry, who make exactly 50,000. So it's less than or equal to 50,000 dollars.
Now what we're going to do is head on over to a different table. We're going to do the demographics table. Make sure I spell that right, and let's add our semicolon. Let's run this, and what we want to look at is the gender really quick. So we're going to say WHERE the gender is equal to, we'll do in quotes "female." If we run this, we get all the genders that are equal to female. But we do have something called the not equal to, and it looks like this: it's an exclamation point and an equal sign. This is going to say WHERE the gender is not equal to female. So if we run this, you'll notice that the gender is all male now.
So far, we've worked with things like integers, which are numbers, and we've worked with characters or strings like names. But there's a different type of data type as well in here; we have a date column for these birth dates. Now in the WHERE clause, we can also filter on birth dates. Let's come over here and we'll say birth_date is greater than, and within quotes, we'll say "1985-01-01." This is kind of the standard default date format within MySQL, which is year, month, and day. If we go ahead and run this, we can also take all the people who are born greater than 1985. So all these dates are greater than 1985.
Now the next thing that I want to take a look at is logical operators in the WHERE clause. So logical operators are things like AND, OR, and NOT. Now these are called logical operators. So logical operators allow us to have different logic, and let's take a look at how this works exactly. Let's copy this down because we already have this one written out. We're saying WHERE the birth_date is greater than 1985. We can also say WHERE the gender is equal to male. So we can say AND gender is equal to "male."
So we're adding a different complexity or an additional conditional statement within our WHERE clause. Let's go ahead and run this. Now we're only selecting birth dates that are greater than 1985 and where the gender is equal to male. Only the rows that fulfill both of those are returned. Now the AND says both this and this have to be true, but we could change this. We could say OR. What this means is either this one has to be true or this one has to be true in order for it to be returned.
So let's go ahead and run this. You'll notice that Jerry Gergich was born much before 1985, but since he has a male gender, he is in our output. We could also use the NOT operator by saying OR NOT gender equal to "male." So now what this is saying is the birthday could be greater than 1985 or it could not be equal to male, which is female. So if we look at Leslie Knope, she was born before 1985, but because she is female, she is in the output.
Now, like we talked about in the last lesson, there is something called PEMDAS, and that actually applies to these logical operators as well. So if we run this entire table, let's go ahead and run this. If we're looking at this entire table, let's say we want to get someone very, very specific. Let's say we're going to do WHERE the first_name is equal to "Leslie," and their age has to be equal to 44. That's extremely specific, and we can actually just do it like this. We don't need quotes for integers; we could just do the number if we'd like to. This is very specific; there's only one person.
But if we put this in parentheses, we can add an OR over here. We could say OR the age is greater than, let's just do 55. Let's go ahead and run this and then we'll take a look at it. So within these parentheses, we have an AND operator. What that means is both this condition has to be met and this condition has to be met, and that's only one person, that's Leslie Knope. But then outside of these parentheses, we have another conditional statement OR the age is greater than 55.
So what we're saying within these parentheses is that this is an isolated conditional statement. If this is true, then in our output, it'll be returned. But then we have an OR condition which says OR someone with the age of greater than 55 can also be in the output. So these parentheses can be really helpful when you're actually using it in the WHERE clause with these ANDs, ORs, and NOTs.
Now, I want to take a look at just one more thing, and let's bring this down here and let's get rid of this entire thing. Now the last thing I want to take a look at is a LIKE statement. Now the LIKE statement is super unique because we can look for specific patterns. We're not necessarily looking for an exact match. Like here, if we said WHERE first_name is equal to "Jerry," if we're looking for Jerry, it has to be exactly Jerry. But if we take this out, say "Jar," and then we run it, we get no output; it has to be an exact match.
But here's where the LIKE statement comes in because we can actually say LIKE "Jer" and we can add two special sequences or special characters within our LIKE statement. So those special characters are the percent sign and the underscore. The percent sign means anything, and the underscore means a specific value. Let's see how that actually works. So what we're going to do is we're going to say LIKE "Jer%"—that's the first one in this LIKE statement. What this says is the first name is LIKE starting with "Jer," but that has anything after it. It doesn't matter what it is as long as it has "Jer" at the very beginning; it will be returned.
Let's go ahead and run this. Now the only person who starts with "Jer" is Jerry. But what if I took the "J" out of here? Now it's saying it starts with "Er," and that's not anybody. What we can do is we can add another percent at the beginning. This is going to say anything comes before, anything comes after; all we're looking for is "Er" somewhere in their name. Let's go ahead and run this. There's still only one person, and that's Jerry.
Now let's come up here and let's get rid of this and let's say we're looking for everyone's name who starts with "A." We do that really easily by saying "A%." All that says is it starts with "A." We don't have a percent sign before it, which would say this string just has to have an "A" somewhere in it. If we have it like this, this means an "A" has to come at the beginning. Let's go and run this.
In our output, we have April and Andy. Now let's take a look at the underscore. If we get rid of this percent sign and we do two underscores, this is going to say it starts with an "A" and then it has two characters after it—no more, no less. So if we run this, "N" is going to be the only person who's returned because she has an "A" and then two characters after it. Now if we want Andy, we can specify that by doing another underscore—that's one, two, three—and now Andy is the only one in our output.
Now there was also April in there, but she had more than three characters. But we can actually get her in our output by doing a percent sign. So we can combine both the underscore and the percent sign, and this is going to say it starts with an "A" as one, two, three characters, and then it can have anything after that. So it just has to have at least an "A" and have one, two, three characters after it. Let's run it.
Now you can see April comes into here because she does have an "A," and the "P," "R," and "I" are the three next characters, but then we have a percent sign that allows that "L" to be in the output as well. Now we don't just have to do this with strings or text like April and Andy; we could also do this with birth dates. For example, Andy's birthday is 1989. We could say WHERE the birth_date is LIKE, let's say we want to look at everyone who is 1989, who was born in 1989. Let's go and run this, and Andy's the only person born in 1989.
But again, we looked at the year at the very beginning. So that is how the LIKE statement works; it looks for a specific sequence within that column that you can search for. So it doesn't have to be an exact match as long as it has that specified sequence that you've put in there anywhere within that cell or that column.
So that is everything that we're going to look at for the WHERE clause. In the next lesson, we're going to take a look at the GROUP BY and the ORDER BY within MySQL.