Filtering

A table might have millions of rows. You almost never want all of them. WHERE is how you tell the database which ones you actually care about - it appears in nearly every SELECT you will ever write.

The database evaluates your condition against each row. Rows where the condition is true come through; everything else is discarded before anything reaches you.

Comparison operators

The six comparison operators cover most situations:

SELECT name, salary
FROM employees
WHERE salary > 80000;

If you come from Python or JavaScript, this will trip you up at least once: SQL uses a single = for equality comparisons, not ==. Use != or <> for "not equal" - both are valid, though != is more common in PostgreSQL.

Combining conditions with AND and OR

Chain conditions with AND (both must be true) and OR (either must be true):

SELECT name, department, salary
FROM employees
WHERE department = 'Engineering'
  AND salary > 90000;

Mixing AND and OR in the same clause is where bugs hide. AND binds more tightly than OR - same precedence rule as multiplication vs. addition - but that surprises people every time. Use parentheses to make intent explicit:

-- Without parentheses, this reads as:
-- department = 'Engineering' AND (salary > 90000 OR start_date > '2023-01-01')
WHERE department = 'Engineering'
  AND salary > 90000
  OR start_date > '2023-01-01';

-- With parentheses, unambiguous:
WHERE (department = 'Engineering' OR department = 'Data')
  AND salary > 90000;

When in doubt, add parentheses. They cost nothing and prevent a whole category of silent bugs that are genuinely hard to track down.

IN and NOT IN

IN is cleaner than chaining a long list of OR conditions:

-- Verbose
WHERE department = 'Engineering' OR department = 'Data' OR department = 'Design'

-- Cleaner
WHERE department IN ('Engineering', 'Data', 'Design')

NOT IN excludes the listed values. There is a trap here: if any value in the NOT IN list is NULL, the entire expression evaluates to NULL and no rows come back at all. The NULL guide covers why. For now, just remember that NOT IN and NULL are a bad combination.

LIKE for pattern matching

LIKE matches strings against a pattern using two wildcard characters:

WHERE email LIKE '%@gmail.com'   -- ends with @gmail.com
WHERE name LIKE 'A%'             -- starts with A
WHERE phone LIKE '555-____'      -- 555- followed by exactly 4 characters

LIKE is case-sensitive in PostgreSQL. Use ILIKE for a case-insensitive match (PostgreSQL-specific, but very handy):

WHERE name ILIKE 'alice%'  -- matches "Alice", "ALICE", "alice"

BETWEEN

BETWEEN tests whether a value falls within a range, inclusive on both ends:

WHERE hire_date BETWEEN '2020-01-01' AND '2022-12-31'
-- equivalent to:
WHERE hire_date >= '2020-01-01' AND hire_date <= '2022-12-31'

The two forms are interchangeable. BETWEEN reads more naturally for date ranges; explicit comparisons are clearer when the bounds are asymmetric - say, one inclusive and one exclusive.

NOT

NOT inverts any condition and pairs naturally with LIKE, IN, BETWEEN, and EXISTS:

WHERE name NOT LIKE 'A%'
WHERE department NOT IN ('Sales', 'Marketing')
WHERE salary NOT BETWEEN 50000 AND 100000

For equality, != is cleaner than wrapping everything in NOT. But NOT LIKE, NOT IN, and NOT BETWEEN are standard - you will see them constantly and they read exactly as you would expect. The only one to watch is NOT IN with nullable columns, which you already know about.