WHERE

Beginner

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:

SELECT *
FROM students
WHERE class = '7A'

and this query will retrieve all columns (*) from the students table where the class column has the value '7A'.

Take another example: let’s say we wanted to select all students whose first_name is equal to “Rebecca”. We’d write:

SELECT *
FROM students
WHERE first_name = 'Rebecca'

Using comparison operators

It’s also possible to use the comparison operations > and < to write our WHERE clauses. The following query, for instance, fetches all the students whose age is greater than 12…

SELECT *
FROM students
WHERE age > 12

… and the following query will select all students whose age is less than or equal to 14. 

SELECT *
FROM students
WHERE age <= 14

The BETWEEN operator

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).

SELECT *
FROM students
WHERE age BETWEEN 12 AND 14

Practice

Now it's your turn! Open the following questions in a new tab to try out the WHERE operator:

Previous Next