 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 gonna 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 gonna say first underscore name. So we're saying where the first name will 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. Nope. If we run this, there's only gonna 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 gonna run it with this one right here. It's gonna 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 gonna say where the salary and I'm gonna 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 which 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 you're 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 can 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 it's not 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'll say birth underscore 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 my sequel 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 of 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 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 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 we could say or what this means is 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 Gurgic 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 we look at Leslie nope 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 underscore 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 nope 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'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 and ors and nuts 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 underscore 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 that 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's stills 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 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 and 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 has two characters after it no more no less so if we run this an 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 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 underscore date is like let's say we want to look at everyone who is 1989 or 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 my sequel