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.

🔎 Filtering Rows with 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;

Common Comparison Operators

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.

🔗 Combining Conditions: AND / OR

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;

🎯 Working with Ranges: BETWEEN / NOT BETWEEN

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;

📋 Using IN for Lists

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.

---

💡 Tip:

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.

---

✅ Key Takeaways

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

---

📝 Exercise Overview

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.

---

Ready to practice SQL? Try our coding challenges →