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 let's go down with that one. Let's 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, so we're saying where the first name, we'll say is equal to and let's do quotes and let's 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. And there's 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 and we're 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 let's look at this salary field. So, I'm going to say where the salary, and now I'm going to do a different comparison operator called greater than. So, when 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 when 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 have 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, and now we only have two people who make less than 50,000. That's April and Andy. And 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. 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. And 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. Now, so far, we've worked with things like integers, which are numbers. 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're going to say birth date. Let's say it's 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 greater than or 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, and let's add this, logical operators. So, logical operators allow us to have different logic. 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 the gender is equal, and then we'll say male. So, we're adding a different complexity or an additional conditional statement within our WHERE clause. Let's go ahead and run this. So, 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 and 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. And we could also use the NOT operator by saying OR NOT gender equal to male. So, now what this is saying is the birth date could be greater than 1985, or it could not be equal to male, which is female. So, if you 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 can just do the number if we'd like to. This is very specific. This is 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. Within these parentheses, if this is true, then in our output it will 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 that we're going 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 J E R, 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 J E R, 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 J E R percent sign. That's the first one in this LIKE statement. What this says is the first name is like starting with J E R, but then has anything after it. Doesn't matter what it is, as long as it has J E R at the very beginning, it will be returned. Let's go ahead and run this. Now, the only person who starts with J E R is Jerry. But what if I took the J out of here? Now it's saying it starts with E R. 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 E R somewhere in their name. Let's go ahead and run this. There still is 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 can do that really easily by saying A percent sign. 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 ahead and run this. In our output, we have April and an Andy. Now let's take a look at the underscore. If we get rid of this percent sign, and we do two underscores. One, two. 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, Ann 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, has 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. So, let's run it. Now you can see April comes into here because she does have A, 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 birth date is 1989. We could say where the birth date is like, let's say, we want to look at everyone who is 1989 or born in 1989. Let's go ahead 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.
