Explains how to filter the select query using a WHERE clause, including combining multiple filters with and, or, and between
- [Instructor] So now we know how to adjust the number of fields that are returned in a query. We just list the field names and separate them with commas. But so far we've always returned all of the rows in the table. Now, what if we want instead to only return actors whose name is Penelope? Then we would place a filter on the data using the WHERE keyword. Like so. So we can say WHERE first_name equals 'PENELOPE'.
And if we click Go, we can see we now have four rows instead of 200 returned to us. Now, what if we wanted to return all actors except those called Penelope? To indicate a Not in MySQL, we use a less-than and greater-than sign, like so. And you can see that's returned us 196 rows, as we'd expect. Equal signs and this not-equal sign are called operators.
These two brackets taken together are called the Not operator. Using these two brackets should work in any version of SQL, but you can alternatively use an exclamation mark followed by an equals sign if you're using a version other than MySQL. So either of those can work as the Not operator. Notice that the word Penelope is within single quotation marks. Single quotes should be used to enclose text.
They're not the same as the backticks that we saw earlier. Backticks were optional earlier around the table name Actor. But leaving out single quotation marks around the word Penelope is not an option. Without quotation marks, your database would think that Penelope was a command, like Select or From, and then it would throw an error. So quotation marks tell the database it's dealing with text, also known as a string. Some types of SQL can accept single or double quotes, while others will only accept single quotes.
It's a good practice to just use single quotes. So, what if you want to filter on something other than a string? Let's say you want to select all actors with an ID of less than five. There are lots of new operators you can use when you're dealing with a numeric data type. So if we run that, we can see that we are returning all the actors with an ID of less than five. Now, if we wanted a range of numbers here, we can add further filters using AND after the WHERE clause.
Let's pop that up there. We can say WHERE actor_id is less than five AND actor_id is greater than three. And that's returned us just the one row where actor ID is four. There is an alternative method here called BETWEEN, although I never use it. I find it more precise to use AND. But here's how it works. We would say WHERE actor_id BETWEEN three and five.
And notice that's now returned us three rows. So BETWEEN and AND in MySQL is inclusive. But you wouldn't know it just by looking at it. This ambiguity is why I stick to using operators with AND. Now, notice that we haven't needed quotation marks or in fact anything around these numbers here. Numeric values never need anything around them in any form of SQL. We can also make our filters up using the keyword OR.
So we could say WHERE actor_id less than five OR first_name is 'PENELOPE'. And we can check that's worked by looking in these two columns here. And we can easily add more OR statements too. So we could say WHERE first_name is 'PENELOPE' OR first_name is 'NICK' OR first_name is 'ED'.
But as we'll see later, we do need to execute some care when combining ANDs with ORs.
Join Emma Saunders as she shows you how to design and write simple SQL queries for data reporting and analysis. Review the different types of SQL, and then learn how to filter, group, and sort data, using built-in SQL functions to format or calculate results. Learn a bit about data types and database design. Discover how to perform more complex queries, such as joining data together from different database tables. Last but not least, Emma shows how to save your queries as views, so you can run them again and again.
- Using different versions of SQL
- Retrieving data with SELECT statements
- Filtering and sorting your results
- Transforming results with built-in SQL functions
- Grouping SQL results
- Merging data from multiple tables
- Identifying data types, and how to make sense of your database design
- Saving SQL queries