Filtering with WHERE

WHERE filters which rows a query returns, based on a condition evaluated against each row:

SELECT name, email FROM users WHERE country = 'Canada';

Only rows where the condition is true make it into the result. Comparison operators work as you'd expect: =, != (or <>), <, >, <=, >=.

Combining conditions

AND, OR, and NOT combine multiple conditions, and parentheses control precedence exactly like in any programming language:

SELECT * FROM users WHERE country = 'Canada' AND is_active = TRUE;

SELECT * FROM users WHERE country = 'Canada' OR country = 'USA';

SELECT * FROM orders WHERE NOT (total < 10);

AND binds tighter than OR, so WHERE a = 1 OR a = 2 AND b = 3 means a = 1 OR (a = 2 AND b = 3) — when mixing both in one query, use explicit parentheses so the intent is unambiguous to the next person reading it.

IN: matching against a list

IN is shorthand for a chain of ORs against the same column:

SELECT * FROM users WHERE country IN ('Canada', 'USA', 'Mexico');
-- equivalent to: country = 'Canada' OR country = 'USA' OR country = 'Mexico'

BETWEEN: a range, inclusive on both ends

SELECT * FROM orders WHERE total BETWEEN 10 AND 50;
-- equivalent to: total >= 10 AND total <= 50

LIKE: pattern matching text

LIKE matches text against a pattern using two wildcards: % for any number of characters (including zero), and _ for exactly one character.

SELECT * FROM users WHERE email LIKE '%@gmail.com';   -- ends with @gmail.com
SELECT * FROM users WHERE name LIKE 'A%';              -- starts with A
SELECT * FROM users WHERE name LIKE '_da';             -- exactly 3 chars, ending in "da"

NULL needs its own operators

NULL represents a missing or unknown value, and it doesn't behave like a normal value in comparisons — NULL = NULL evaluates to NULL (neither true nor false), not TRUE. Comparing anything to NULL with = or != always produces NULL, which WHERE treats as excluding the row:

-- Wrong — this matches zero rows, even if some phone values are actually NULL
SELECT * FROM users WHERE phone = NULL;

-- Right
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

IS NULL and IS NOT NULL are the only correct way to check for NULL — this is one of the most common mistakes beginners (and experienced developers switching from another language) make in SQL.

Filtering handles which rows come back — the next lesson covers ORDER BY, for controlling what order they come back in.