Conditionally Select out Filtered Data with SQL Where

Instructor: [00:00] When we use the select statement as is, we get everything out of our table. If we want to filter the results down to specific rows, we use the where clause. Within our select statement, let's say select out all the columns where the last name equals Clarke.

[00:14] You can think of the select statement as a for loop. We're looping over each row within the table and pulling it out. This where clause is how we can filter down what actually gets returned.

[00:24] Only having one condition within our where clause did the job. However, we could get more specific and add more cases.

[00:31] We can add on to our where clause an and first name equals Tyler to get the same row back. Using the and logical operator means that both cases provided here have to be true for any rows to be returned. We could instead use the or logical operator as well, which means that at least one of the two cases needs to be true.

[00:51] When working with dates and numbers, we can use other operators such as the greater than, say, give us the rows where the create date is greater than May 5th, 2018, which is going to be both rows. We can use the less than, which will give us neither of the two rows, as well as equal, which again will give us neither.

[01:16] There is greater than or equal, which will give us back our two rows, because they're greater than May 5th. There's the less than or equal, which is none. Finally, the not equal, which is going to give us our rows, because both rows have a create date that's not equal to May 5th.

[01:34] There are a couple of other special operators as well. I'm going to insert a row into our table that has no last name and first name, as well as a different create date value. With this in there, we can say select out the rows where the first name is null. This is going to give us that one row.

[01:49] When working with null values, which signify unknown, we cannot use the equal sign, because it will return null itself, not a true or false value. We have to use this is operator. Another for operator is between. Say select from users where create date is between 2018, May 1st and 2018, September 1st. This is going to give us all three rows, because the create date falls between this range.

[02:16] We can exclude the August date by changing the last range to end July 1st. Finally, there is the in operator, which gives us all the rows where the last name column equals any of the values within this list. The list is separated by commas and we can work with strings and numbers within these parenthesis.

[02:36] As we see here, we did not get that third row, which had no values for the last name and first name columns.