WHERE Clauses

SELECT chooses columns; WHERE chooses rows. It's the clause that turns a table-dumper into a question-answerer, and its condition language is small enough to learn completely in one sitting — so let's.

The shape

SELECT name, population
FROM cities
WHERE country = 'JP';

The engine conceptually walks every row, evaluates the condition, and keeps the rows where it's true. Comparison operators: =, <> (not equal — != also works), <, <=, >, >=. Note it's a single = for equality — SQL has no assignment inside queries to confuse it with.

Combining conditions

WHERE country = 'JP' AND population > 3000000
WHERE country = 'JP' OR country = 'DE'
WHERE NOT (country = 'JP')

AND binds tighter than OR — a OR b AND c means a OR (b AND c), which is almost never what the sentence in your head meant. Parenthesize any mixed AND/OR condition; it costs nothing and prevents the classic "why is this row here?" bug.

The convenience operators (that graders love)

WHERE country IN ('JP', 'DE', 'FR')          -- any of these — cleaner than OR chains
WHERE population BETWEEN 1000000 AND 5000000 -- inclusive on BOTH ends
WHERE name LIKE 'San%'                       -- pattern match: % = any run of chars
WHERE name LIKE '_a%'                        -- _ = exactly one char: 2nd letter is 'a'

LIKE's two wildcards do all the work: '%burg' ends with burg, '%an%' contains an. (In SQLite, LIKE is case-insensitive for ASCII by default — 'tokyo' LIKE 'TOKYO' is true; the exact-match operator = is always case-sensitive.)

NULL: the special case that isn't equal to anything

A NULL (missing value) never satisfies = — not even NULL = NULL. Comparing anything with NULL yields unknown, and WHERE keeps only true. So:

WHERE population = NULL     -- matches NOTHING, ever (the classic bug)
WHERE population IS NULL    -- the correct spelling
WHERE population IS NOT NULL

If a row you can see in the table refuses to match your WHERE, a NULL is usually hiding in it. (Chapter 4 gives NULL a full lesson — for now: IS NULL, never = NULL.)

Your exercise: Filter by Country

Set up the table, then select with the right WHERE. The skill being graded is translation: the problem states a condition in English ("cities in country X with population above Y"), and you render it as operators — country = 'X' AND population > Y. Read the boundary words carefully: "above 3 million" is >, "at least 3 million" is >=, and BETWEEN includes both ends. One operator off is one row off, and the grader counts rows.