WHERE: Filtering Rows
Keep only the rows you want: comparisons, AND, OR and NOT, ranges with BETWEEN, lists with IN, text patterns with LIKE, and NULL, the value that means "unknown", which trips up everyone at least once.
Filtering with WHERE
WHERE keeps only the rows for which a condition is true. It goes after FROM:
The comparison operators: = (equal: one equals sign in SQL), <> or != (not equal), <, >, <=, >=. Text comparisons need single quotes and are case-sensitive in most databases ('brisbane' does not match 'Brisbane').
AND, OR, NOT and brackets
Combine conditions with AND (both must be true), OR (at least one) and NOT. AND is evaluated before OR, so use brackets whenever you mix them:
Without brackets, a OR b AND c means a OR (b AND c), which is often not what you meant.
BETWEEN, IN and LIKE
| Operator | Example | Means |
|---|---|---|
BETWEEN a AND b | price BETWEEN 20 AND 50 | 20 ≤ price ≤ 50 (both ends included) |
IN (…) | country IN ('Nepal', 'Canada') | equal to any value in the list |
LIKE | name LIKE 'S%' | text pattern: % = any characters, _ = exactly one |
NOT … | status NOT IN ('cancelled') | the opposite |
Patterns: '%book%' contains "book", 'S%' starts with S, '%a' ends with a, '____' is exactly 4 characters. In SQLite, LIKE ignores upper and lower case for English letters; in PostgreSQL it does not (use ILIKE there).
Dates stored as text in the ISO format 'YYYY-MM-DD' compare correctly: order_date BETWEEN '2025-03-01' AND '2025-03-31' is March 2025.
Try it: filters
Run the presets, then write your own: customers from Nepal, products whose name contains "desk", orders in June.
Interactive: this part runs in your browser.
NULL: the value that means "unknown"
NULL means "no value" or "unknown": a customer whose city we do not know has NULL in the city column. NULL is not zero and not an empty string, and it behaves differently from every other value:
- Any comparison with NULL is neither true nor false but unknown, and WHERE only keeps rows where the condition is true. So
WHERE city = NULLreturns nothing, even for rows where city is NULL. - Test for NULL with
IS NULLandIS NOT NULL. WHERE city <> 'Sydney'does not return the rows where city is NULL either. If you want them, addOR city IS NULL.- Arithmetic with NULL gives NULL:
5 + NULLis NULL. Replace NULL with a default usingCOALESCE(city, 'unknown')(see the next lesson).
Practice: check yourself
Write the query, then press ✔ Check my answer: your query runs on a fresh copy of the shop database and its result is compared with the correct one. ▶ Run just shows your result. Each run starts from the original data, so you cannot break anything.
Tables: customers(customer_id, name, city, country, joined) · products(product_id, name, category, price) · orders(order_id, customer_id, order_date, status) · order_items(order_id, product_id, quantity, unit_price)
Show the name and city of the customers whose country is Nepal.
Show the name and price of products in the Electronics category that cost between 40 and 300 (inclusive).
Show the name of the customers whose city is unknown (NULL).
Show the order_id and order_date of orders whose status is pending or shipped, placed in the second half of 2025 (from 2025-07-01).
Summary
Key words
- WHERE
- keeps the rows for which a condition is true
- condition
- a test that is true, false or unknown
- AND / OR / NOT
- combine conditions; AND before OR
- BETWEEN
- a range, ends included
- IN
- matches any value in a list
- LIKE
- text pattern with % and _
- NULL
- a missing or unknown value
- IS NULL
- the only correct test for NULL
Common mistakes
WHERE city = NULL: always useIS NULL.- Forgetting that
<>also drops NULL rows. - Mixing AND and OR without brackets.
- Text without quotes (
WHERE country = Nepallooks for a column named Nepal) or with double quotes. LIKE 'book'without % matches only the exact word "book".
Quiz
1. Which condition finds customers whose city is unknown?
2. price BETWEEN 10 AND 20 includes a price of exactly 20?
3. Which pattern matches names that contain "lamp" anywhere?
4. WHERE a OR b AND c is read as…
5. A customer has city NULL. Is the row returned by WHERE city <> 'Sydney'?