Up until this point, we haven’t really looked at how to filter our data tables. We’ve used
LIMIT to select the top rows, but
LIMIT doesn’t provide a way to apply complex filters to our data table.
This is where the
WHERE clause comes in. Using
WHERE, we can apply one or more filters to our tables and thereby specify which rows we want to fetch.
To see this in action, consider the
students table we used in a previous tutorial:
Let’s say that we wanted to view a list of all the students in class “7A”. We could write:
WHERE class = '7A'
and this query will retrieve all columns (
*) from the
students table where the
class column has the value
Take another example: let’s say we wanted to select all students whose
first_name is equal to “Rebecca”. We’d write:
WHERE first_name = 'Rebecca'
Using comparison operators
It’s also possible to use the comparison operations
< to write our
WHERE clauses. The following query, for instance, fetches all the students whose
age is greater than 12…
WHERE age > 12
… and the following query will select all students whose
age is less than or equal to 14.
WHERE age <= 14
In situations where we want to filter based on a range of values, we can use the
BETWEEN operator. The following query, for instance, will retrieve all rows where the student’s age is between 12 and 14 (inclusive).
WHERE age BETWEEN 12 AND 14
Now it's your turn! Open the following questions in a new tab to try out the