Lesson 3 of 20
In progress
WHERE: filtering rows
Compare values, combine conditions, match text with LIKE, and handle NULL without falling into three-valued logic traps.
Filtering is where SQL starts answering questions instead of dumping tables. It is also where the most common beginner bug lives: a condition that silently matches nothing because of NULL.
What you will learn
- Combine comparisons with
AND,ORandNOT. - Use
IN,BETWEENandLIKEcorrectly. - Predict what
NULLdoes in a condition.
Comparisons
Editable. Runs in your browser.
INSERT INTO orders (id, customer, city, amount_paise, status, placed_on) VALUES
(1, 'Ana', 'Pune', 125000, 'paid', '2025-02-10'),
(2, 'Ravi', 'Delhi', 89000, 'pending', '2025-02-11'),
(3, 'Meera', NULL, 240000, 'paid', '2025-02-12'),
(4, 'Sameer', 'Pune', 45000, 'cancelled', '2025-02-13'),
(5, 'Ana', 'Mumbai', 310000, 'paid', '2025-02-14');
SELECT id, customer, amount_paise
FROM orders
WHERE amount_paise > 100000;
Press Run to see the output.
Comparisons use =, <> (or !=), <, >, <=, >=. They work on numbers, text and dates. Text comparison depends on collation, which determines whether case matters; the default in SQLite is case sensitive for =.
Filtering on a computed value repeats the expression rather than the alias, because WHERE is evaluated before the SELECT list is named:
Editable. Runs in your browser.
Press Run to see the output.
Some databases allow the alias in WHERE; SQLite does not, and relying on it makes the query non-portable. Repeat the expression or use a subquery.
Combining conditions
Editable. Runs in your browser.
Press Run to see the output.
AND requires both sides, OR requires either, NOT inverts. Precedence puts AND before OR, so this:
WHERE status = 'paid' OR status = 'pending' AND amount_paise > 100000
means paid rows, plus pending rows above the amount. If you meant either status with the amount limit, use parentheses. Parentheses are free and remove the question.
Lists and ranges
Editable. Runs in your browser.
SELECT id, amount_paise
FROM orders
WHERE amount_paise BETWEEN 50000 AND 200000;
Press Run to see the output.
IN is shorter than a chain of ORs, and it also handles a list produced by a subquery. BETWEEN is inclusive on both ends, which surprises people who expect an exclusive upper bound. For dates, BETWEEN '2025-02-01' AND '2025-02-28' includes the 28th but not the time after it if the column has a time; use >= and < with the next day instead.
Matching text
Editable. Runs in your browser.
SELECT id, customer
FROM orders
WHERE customer LIKE '%e%';
Press Run to see the output.
% matches any run of characters, _ matches exactly one. LIKE is case insensitive for ASCII in SQLite by default; in PostgreSQL it is case sensitive and ILIKE is the insensitive form.
A leading % prevents an index from being used, because the engine cannot know where the match starts. For a search box over large tables, that is the difference between milliseconds and seconds.
The NULL trap
Editable. Runs in your browser.
Press Run to see the output.
This returns no rows, ever. NULL means unknown, so city = NULL evaluates to unknown rather than true, and WHERE keeps only true.
Editable. Runs in your browser.
Press Run to see the output.
IS NULL and IS NOT NULL are the only correct tests. The same applies to <>: city <> 'Pune' excludes rows where city is null, because the comparison is unknown. If you want them, add OR city IS NULL.
NOT IN has a sharper version of this problem. x NOT IN (1, 2, NULL) is never true, because comparing with the NULL yields unknown. Avoid NULLs in NOT IN lists, or use NOT EXISTS.
Order of evaluation
WHERE is not necessarily evaluated left to right. The query planner may reorder conditions, so do not rely on short-circuit behaviour to guard against a division by zero or a bad cast. Use CASE when a guard is needed: a condition that is always evaluated, in a fixed position, is easier to reason about than an order you cannot see.
Using != or <> to find "everything except this value" and forgetting that NULL rows disappear. Add OR column IS NULL when those rows belong in the answer.
Find paid orders from Pune or Mumbai above 100000 paise, and separately find orders with no city recorded. Then explain why WHERE city <> 'Pune' misses one row.
Show solution
Editable. Runs in your browser.
SELECT id, customer
FROM orders
WHERE city IS NULL;
Press Run to see the output.
The second query returns Meera’s order. WHERE city <> 'Pune' returns Sameer, Ravi and Ana in Mumbai but not Meera, because NULL <> 'Pune' is unknown, not true. To include her, write WHERE city <> 'Pune' OR city IS NULL.
Recap
ANDbinds tighter thanOR; add parentheses when the intent matters.INandBETWEENshorten range tests, andBETWEENincludes both ends.NULLis unknown: useIS NULL, and expect it to vanish from<>andNOT IN.
Next, you will sort and page through results with ORDER BY and LIMIT.
Is WHERE or HAVING faster?
Can I filter on a CASE expression?
Progress is saved in this browser only. No account, no email.