Queries that return every row in a table are rarely useful. The WHERE clause lets you filter down to the rows you actually care about, and it’s applied early in the query execution process.

This chapter uses the users and streams tables alongside the artists, albums, and tracks data from the previous chapter. Insert these rows to follow along:

The users table has a mix of subscription tiers (free, premium, family), countries (US, GB, KR, JP, and one NULL), and three users with NULL timezones. This variety gives us enough data to demonstrate every filtering concept in this chapter.

WHERE evaluates a condition for every row in the table. Rows where the condition is true get included in the result. Rows where it is false (or unknown) get excluded.

The simplest conditions use comparison operators:

Equality

The most common filter.

Example: Find all premium users

String comparisons in SQL are case-sensitive by default in PostgreSQL and most databases. WHERE country = 'us' would match nothing if the data stores 'US'.

Inequality

SQL supports two operators for “not equal”:

Both are supported in databases like PostgreSQL, MySQL, and Microsoft SQL Server.

This query returns users on premium or family plans, excluding free users.

Both styles work, but it’s best to choose one and stay consistent across your codebase. In practice, <> is often preferred because it follows the SQL standard.

Numeric Comparisons

Comparison operators work naturally with numbers.

Example: Find streams where the user listened for more than 200 seconds

You can also compare computed expressions. For example, to find tracks longer than 4 minutes:

Here, the expression duration_seconds / 60.0 is calculated for each row before the comparison is applied. Using 60.0 instead of 60 is important because it forces decimal division and avoids integer truncation.

String Comparisons

String values must be wrapped in single quotes. Double quotes are for identifiers (column names, aliases), not values.

PostgreSQL treats double-quoted identifiers as case-sensitive column names. WHERE country = "US" looks for a column called US, not the string value 'US'. MySQL is more lenient here but relying on that leads to non-portable queries.

Comparison operators on strings use lexicographic (dictionary) ordering:

This returns usernames that come after 'emma_dev' alphabetically: frank_j, gina_null, hiro_jp. Lexicographic comparison works character by character based on the column's collation (the rules that define sort order for a character set).

Date Comparisons

Dates behave like numbers in comparisons. Later dates are "greater than" earlier dates.

This returns users who signed up on or after January 1, 2024: dave_kr, emma_dev, frank_j, and gina_null. Date literals are strings in 'YYYY-MM-DD' format, and the database implicitly casts them to the date type for comparison.

Combining date comparisons creates date ranges:

This finds users who signed up in the second half of 2023. Using >= on the start and < on the end is the half-open interval pattern. It avoids double-counting boundary dates and works cleanly with timestamps too. The next chapter covers the BETWEEN operator, which provides a more concise syntax for range queries.

A single condition is often not enough. Real-world queries usually combine multiple filters using logical operators.

AND

AND requires both conditions to be true.

Example: Find premium users from the US

Only alice_m matches because she is the only user who is both premium and from the US.

You can chain as many AND conditions as you need:

Every AND narrows the result set further. Think of AND as an intersection: only rows satisfying all conditions survive.

OR

OR requires at least one condition to be true.

Example: Find users from the US or GB

This returns alice_m, bob_jones, emma_dev (US) and carol_uk, frank_j (GB). OR widens the result set. A row passes if it satisfies either condition (or both).

NOT

NOT inverts a condition.

Example: Find users who are not on the free tier

This is equivalent to WHERE subscription_tier <> 'free'. NOT is more useful when negating complex expressions or operators that do not have a direct inverse (like NOT EXISTS or NOT IN, covered in later chapters).

Combining AND and OR

Real filters often mix AND and OR. This is where things get tricky:

This query does not do what it looks like. It does not find "users from the US or GB who are premium." The next section explains why.

AND has higher precedence than OR. The database evaluates AND conditions first, then OR, just like multiplication happens before addition in arithmetic. The query above is actually evaluated as:

This returns all US users (regardless of tier) plus GB users who are premium.

The following diagram shows how the database groups the conditions:

AND binds tighter, so country = 'GB' and subscription_tier = 'premium' form a group first. Then OR combines that group with country = 'US'.

Here is how each user evaluates against this query:

Notice that bob_jones and emma_dev are included even though they are on the free tier. That is the precedence problem.

The Fix: Use Parentheses

Parentheses override precedence and make intent explicit:

Now the OR is evaluated first (US or GB), and then AND filters that result to only premium users. This returns alice_m, carol_uk, and frank_j.

A good rule: whenever you mix AND and OR, use parentheses. Even if the precedence already does what you want, parentheses make the intent clear to anyone reading the query.

This is one of the most frequently tested SQL concepts in interviews. Consider finding users who have not set their timezone:

This query returns zero rows. Not because every user has a timezone, but because comparing anything to NULL with = produces UNKNOWN, not TRUE. And WHERE only includes rows where the condition is TRUE.

NULL in SQL means "unknown value." It is not zero, not an empty string, not false. It is the absence of a value. Because the value is unknown, asking "is this unknown value equal to NULL?" has no definitive answer. The result is UNKNOWN.

The same applies to inequality:

No comparison operator (=, <>, >, <, >=, <=) produces TRUE when one side is NULL. They all return UNKNOWN, and UNKNOWN rows are excluded from results.

IS NULL and IS NOT NULL

SQL provides dedicated operators for NULL checks:

This correctly returns emma_dev, frank_j, and gina_null.

The inverse finds users who do have a timezone:

This returns alice_m, bob_jones, carol_uk, dave_kr, and hiro_jp.

NULL in Other Comparisons

The NULL trap is not limited to direct NULL checks. Any comparison involving a NULL value produces UNKNOWN:

This returns alice_m, bob_jones, and emma_dev. It does not return gina_null, even though you might think "well, gina's country is not US so she would fail the filter anyway." The distinction matters when you negate the condition:

This returns carol_uk, dave_kr, frank_j, and hiro_jp. It does not return gina_null. Neither = 'US' nor <> 'US' includes rows where country is NULL. The row falls through both filters.

If you want all non-US users including those with NULL countries, you need to handle NULL explicitly:

This returns carol_uk, dave_kr, frank_j, hiro_jp, and gina_null.

As covered in the previous chapter, WHERE is the second step in the database's execution pipeline:

WHERE runs immediately after FROM, which has two practical consequences:

You Cannot Use SELECT Aliases in WHERE

Because SELECT runs at step 5 and WHERE runs at step 2, aliases do not exist when WHERE evaluates:

You must repeat the expression:

You Cannot Use Aggregate Functions in WHERE

Aggregates like COUNT, SUM, and AVG operate on groups, but groups are not formed until step 3 (GROUP BY). WHERE has no groups to aggregate:

The correct approach uses HAVING, which runs after GROUP BY: