In the previous lesson, we explored how to retrieve data using the SELECT statement. Now it’s time to make our queries smarter by filtering the data to return only the rows we’re interested in. This is where the WHERE clause comes into play.
Imagine you’re working with a large dataset of Oscar-winning movies. Instead of scrolling through every single film, wouldn’t it be nice to ask questions like:
Which movies were directed by James Cameron?
What Oscar-winning dramas came out after 2000?
Which films were longer than 2 hours and released in the 2000s?
All of these questions can be answered using WHERE.
The WHERE clause is used to apply conditions to your query results. SQL checks each row to see if it matches the condition(s)—and only includes the ones that do.
Basic format:
SELECT column1, column2
FROM oscar_movies
WHERE condition;
| Operator | Description | Example |
|---|---|---|
= |
Equal to | director = 'James Cameron' |
!= |
Not equal to | year != 1994 |
< |
Less than | year < 2010 |
> |
Greater than | rating > 8.5 |
<= |
Less than or equal to | length_minutes <= 120 |
>= |
Greater than or equal to | year >= 2000 |
You can use comparison operators such as =, !=, <, >, <=, and >= to define the condition. For example, you could filter for movies directed by a specific person, or movies released after a certain year.
To filter based on more than one condition, you can use logical operators like AND and OR. For instance, you might want to return movies that were released after the year 1999 and have a runtime longer than 100 minutes. Or, you might look for movies released in either 1997 or 2009. Combining conditions gives you more control over the data you retrieve.
You can combine multiple conditions using AND and OR:
SELECT column, another_column
FROM oscar_movies
WHERE condition AND/OR another_condition;
If you're checking whether a value falls within a certain range—like a span of years—you can use the BETWEEN operator. It’s inclusive of both the start and end values.
To get the opposite effect, you can use NOT BETWEEN.
| Operator | Condition | SQL Example |
=, !=, <, <=, >, >= |
Standard numerical operators | col_name != 4 |
BETWEEN … AND … |
Number is within range of two values (inclusive) | col_name BETWEEN 1.5 AND 10.5 |
NOT BETWEEN … AND … |
Number is not within range of two values (inclusive) | col_name NOT BETWEEN 1 AND 10 |
IN (…) |
Number exists in a list | col_name IN (2, 4, 6) |
NOT IN (…) |
Number does not exist in a list | col_name NOT IN (1, 3, 5) |
To check if a value falls within a range:
SELECT column, another_column
FROM oscar_movies
WHERE condition BETWEEN another_condition AND another_condition;
When you're checking for multiple specific values in a column (such as multiple genres or directors), use the IN keyword with a comma-separated list. This is cleaner and easier than writing multiple OR conditions.
NOT IN works the same way, but excludes the listed values.
Even though SQL isn’t case-sensitive, it’s a best practice to capitalize SQL keywords like SELECT, FROM, WHERE, and AND. It helps make your queries easier to read and understand.
WHERE lets you filter rows to return only the ones that meet certain criteria.
Use comparison operators, BETWEEN, IN, and logical connectors like AND / OR to build flexible queries.
Filtering not only makes your results easier to interpret—it can also improve query performance.
In this lesson’s exercises, you’ll be working with the oscar_movies table to practice applying constraints with the WHERE clause.
You'll get hands-on practice with:
Finding movies by a specific director
Filtering films by release year ranges
Excluding rows based on a year range
Combining conditions like year and movie length
Exploring the first few records from the table
Filtering by genre and year together
These tasks are designed to help you build confidence writing queries with real-world conditions. Try variations, tweak the values, and see how the output changes. The more you experiment, the better your SQL instincts will become.