Reference · module 2
WHERE
Everything this module explains, on one page.
WHERE
Keep only the rows that match a condition.
- WHERE keeps only the rows that match a condition: SELECT title FROM books WHERE year > 1950;. Rows that fail the condition are left out of the result entirely.
- SQL compares with =, <>, <, >, <= and >=. Equality is a single = and not-equal is <>; most databases, SQLite included, also accept != for not-equal.
- Text is compared in single quotes: WHERE author = 'Austen'. The parts of a query have a fixed order: SELECT, then FROM, then WHERE — the database refuses any other.
The mistake you are about to make
WHERE author = Austen
WHERE author = 'Austen'
The author's name feels like a value the database will understand. Without quotes Austen is a column name, and the query fails with no such column: Austen.
AND, OR, IN, BETWEEN
Combine conditions without being surprised by the order they run in.
- AND needs both conditions, OR needs at least one: WHERE author = 'Austen' AND year > 1816 keeps only Austen's later books. NOT flips a condition.
- AND binds tighter than OR, just as multiplication binds tighter than addition. a OR b AND c means a OR (b AND c), so add brackets when you mean something else.
- IN checks a list: WHERE city IN ('Oslo', 'Rome') is shorter than two ORs. BETWEEN checks a range with both edges included: WHERE year BETWEEN 1800 AND 1900.
The mistake you are about to make
WHERE city = 'Oslo' OR city = 'Rome' AND price < 5
WHERE (city = 'Oslo' OR city = 'Rome') AND price < 5
The condition reads left to right like a sentence. AND runs first: only the Rome rows must be cheap, and every Oslo row comes back at any price.
NULL and LIKE
Find missing values correctly and search text by pattern.
- NULL means the value is unknown or missing. It is not zero and not an empty string, and a comparison with it, even NULL = NULL, is never true.
- So missing values are found with IS NULL, never with = NULL: WHERE phone IS NULL finds customers without a phone, and IS NOT NULL finds those with one.
- LIKE matches text against a pattern: % stands for any run of characters and _ for exactly one. WHERE name LIKE 'A%' finds names starting with A.
The mistake you are about to make
WHERE phone = NULL
WHERE phone IS NULL
= NULL looks like an ordinary emptiness check. A comparison with NULL is never true, so the query does not fail — it returns zero rows even when a hundred are missing.