SQL & Databases
Topic 4 · Querying data

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.

Show:
In this topic
Beginner

Filtering with WHERE

WHERE keeps only the rows for which a condition is true. It goes after FROM:

SELECT name, price FROM products WHERE price < 50;

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').

Beginner

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:

-- Electronics under $100, or anything in Books SELECT name, category, price FROM products WHERE (category = 'Electronics' AND price < 100) OR category = 'Books';

Without brackets, a OR b AND c means a OR (b AND c), which is often not what you meant.

Beginner

BETWEEN, IN and LIKE

OperatorExampleMeans
BETWEEN a AND bprice BETWEEN 20 AND 5020 ≤ price ≤ 50 (both ends included)
IN (…)country IN ('Nepal', 'Canada')equal to any value in the list
LIKEname 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

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.

Beginner

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 = NULL returns nothing, even for rows where city is NULL.
  • Test for NULL with IS NULL and IS NOT NULL.
  • WHERE city <> 'Sydney' does not return the rows where city is NULL either. If you want them, add OR city IS NULL.
  • Arithmetic with NULL gives NULL: 5 + NULL is NULL. Replace NULL with a default using COALESCE(city, 'unknown') (see the next lesson).
SELECT name, city FROM customers WHERE city IS NULL; -- the 2 unknown cities SELECT name, city FROM customers WHERE city = NULL; -- 0 rows: a classic mistake
Beginner

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)

Exercise 1. Customers in Nepal

Show the name and city of the customers whose country is Nepal.

Exercise 2. Mid-priced electronics

Show the name and price of products in the Electronics category that cost between 40 and 300 (inclusive).

Exercise 3. Unknown cities

Show the name of the customers whose city is unknown (NULL).

Exercise 4. Still on the way

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).

Beginner

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 use IS NULL.
  • Forgetting that <> also drops NULL rows.
  • Mixing AND and OR without brackets.
  • Text without quotes (WHERE country = Nepal looks for a column named Nepal) or with double quotes.
  • LIKE 'book' without % matches only the exact word "book".
Check yourself

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'?

Ready