sqlstreak

SQL WHERE: filtering rows

WHERE keeps the rows that match a condition and drops everything else. It runs before grouping and sorting, so it decides which rows the rest of the query ever sees.

Comparisons

Use =, <> (not equal), <, >, <= and >=. Text and dates go in single quotes; numbers don't. ISO dates such as '2026-01-01' compare correctly as text in both PostgreSQL and SQLite.

SELECT name, age, arrived_on
FROM pets
WHERE arrived_on >= '2026-01-01';

AND, OR and parentheses

AND binds tighter than OR. So species = 'cat' OR species = 'dog' AND age > 5 means all cats, plus dogs older than 5. That's rarely what people intend. Use parentheses whenever you mix the two.

SELECT name, species, age
FROM pets
WHERE (species = 'cat' OR species = 'dog') AND age > 5;

IN and BETWEEN

IN (…) is shorthand for several ORs on one column. BETWEEN a AND b includes both ends, so BETWEEN 3 AND 5 matches 3, 4 and 5.

SELECT name, species, weight_kg
FROM pets
WHERE species IN ('rabbit', 'bird')
   OR weight_kg BETWEEN 3 AND 5;

LIKE patterns

LIKE matches text patterns: % means any run of characters and _ exactly one. Watch out: SQLite's LIKE ignores case for English letters, while PostgreSQL's is case-sensitive. PostgreSQL offers ILIKE for case-insensitive matching.

SELECT name
FROM pets
WHERE name LIKE 'P%';

NULL needs IS NULL

NULL means unknown, and comparing anything to unknown gives unknown, which WHERE treats as false. So breed = NULL matches nothing. Always use IS NULL or IS NOT NULL.

SELECT name, species
FROM pets
WHERE breed IS NULL;

Common mistakes

  • Writing = NULL instead of IS NULL. It never matches.

  • Mixing AND and OR without parentheses.

  • Using WHERE to filter on an aggregate like COUNT(*). That belongs in HAVING.

Questions

What's the difference between WHERE and HAVING?+

WHERE filters individual rows before they are grouped. HAVING filters groups after aggregation, so it can use COUNT, SUM and AVG.

Is LIKE case-sensitive?+

In PostgreSQL, yes (use ILIKE to ignore case). In SQLite, LIKE ignores case for ASCII letters by default.

Reading is step one. Now make it stick.

Practise this in a five-minute lesson with instant feedback. No account needed.

Practise it now