Filtering Rows with WHERE

Explanation

SELECT decides which columns to return. WHERE decides which rows to return. Without a WHERE clause, a query returns every row in the table. With one, only rows that match your condition are included.

Consider the movies table:

If you only want movies released after 2018, WHERE release_year > 2018 filters out everything else and leaves you with just the rows that match.

Syntax

SELECT column1, column2
FROM table_name
WHERE condition;

Common comparison operators you can use in a condition:

You can combine multiple conditions with AND (both must be true) or OR (at least one must be true) — we'll go deeper on combining conditions in a later lesson, but it's worth knowing they exist:

SELECT title FROM movies WHERE genre = 'Drama' AND release_year > 2018;

Interactive Example

Common Mistakes

• Forgetting quotes around text values. WHERE genre = Drama will error or behave unexpectedly — it needs to be WHERE genre = 'Drama'.
• Using = for "not equal." Remember it's != or <>, not =! or a single <.
• Assuming WHERE can filter on a column that isn't in the table. The column you filter on doesn't have to appear in your SELECT list, but it does have to exist in the table you're querying.

Best Practices

• Filter on the smallest, most specific condition you can — it makes intent clear to anyone reading the query later.
• Double-check whether a column holds text, numbers, or dates before writing a comparison, since text comparisons need quotes and dates often need a specific format.
• When a query returns unexpected results, comment out or simplify the WHERE clause first — it's the most common source of "wrong" results.

Practice Question

Using either playground above, write a query against the movies table that returns the title and budget_millions of every movie with a budget under 15 million dollars.

Summary

WHERE filters rows based on a condition, using comparison operators like =, !=, >, <, >=, and <=. You can combine simple conditions with AND or OR, though combining several conditions well is a topic we'll cover in more depth soon.

FAQ