Filtering rows with WHERE
Choosing which rows come back, and the one comparison that never behaves the way people expect.
WHERE decides which rows survive. It runs before SELECT picks columns, which
is why you can filter on a column you do not return.
SELECT name
FROM students
WHERE city = 'Hyderabad';
Combining conditions
AND and OR do what you expect, but AND binds tighter than OR. These two
queries do not mean the same thing:
WHERE city = 'Hyderabad' OR city = 'Chennai' AND year = 2;
WHERE (city = 'Hyderabad' OR city = 'Chennai') AND year = 2;
The first returns every student in Hyderabad regardless of year. Write the brackets even when they are redundant — the reader should not have to remember precedence rules to know what you meant.
NULL is the one that catches everyone
NULL means unknown, not empty. Comparing anything to an unknown gives an
unknown, not a true or a false — so WHERE city = NULL matches nothing at all,
including rows where the city really is null.
WHERE city IS NULL;
IS NULL and IS NOT NULL are the only ways to test it. This is the single most
common source of quietly-wrong results in SQL, and it does not raise an error.