Querying Data: SELECT and WHERE · Lesson 5 of 30
SQL WHERE Clause: Filtering Rows
Tables can hold millions of rows; you almost always want only some of them: today's orders, products under ₹500, customers in Pune. The WHERE clause filters rows – only rows for which the condition is true reach the result. Getting WHERE exactly right is the single most important query skill, and its edge cases (especially NULL) are favourite interview questions.
In this lesson
- Comparison operators
- Combining conditions with
AND,OR,NOT IN,BETWEENandLIKE- Working correctly with
NULL
Comparison operators
SELECT name, price
FROM products
WHERE price < 500;+-------------------+--------+ | name | price | +-------------------+--------+ | Clean Code | 450.00 | | SQL in 10 Minutes | 350.00 | | Gel Pen Pack | 120.00 | | A5 Notebook | 90.00 | +-------------------+--------+
| Operator | Meaning | Example |
|---|---|---|
= | equal | city = 'Pune' |
<> or != | not equal | status <> 'cancelled' |
< <= > >= | less / greater | stock >= 100 |
Text and dates are compared too: dates written as 'YYYY-MM-DD' strings compare correctly.
SELECT order_id, order_date, status
FROM orders
WHERE order_date >= '2025-10-01' AND status <> 'cancelled';+----------+------------+-----------+ | order_id | order_date | status | +----------+------------+-----------+ | 107 | 2025-10-03 | delivered | | 108 | 2025-10-10 | pending | | 109 | 2025-10-21 | shipped | | 110 | 2025-11-02 | delivered | | 111 | 2025-11-05 | pending | | 112 | 2025-11-06 | delivered | +----------+------------+-----------+
AND, OR, NOT and parentheses
-- Electronics under ₹1,500 OR anything with more than 200 in stock
SELECT name, category_id, price, stock
FROM products
WHERE (category_id = 1 AND price < 1500) OR stock > 200;+----------------+-------------+---------+-------+ | name | category_id | price | stock | +----------------+-------------+---------+-------+ | Wireless Mouse | 1 | 599.00 | 120 | | USB-C Hub | 1 | 1299.00 | 0 | | Gel Pen Pack | 3 | 120.00 | 500 | | A5 Notebook | 3 | 90.00 | 300 | | Laptop Stand | 1 | 1499.00 | 25 | +----------------+-------------+---------+-------+
Common mistake: AND binds tighter than OR
WHERE city = 'Pune' OR city = 'Delhi' AND signup_date >= '2025-03-01' means "Pune (any date) or (Delhi since March)". If you meant "(Pune or Delhi) since March", you need parentheses. Always add parentheses when mixing AND and OR.
SELECT name, city, signup_date FROM customers
WHERE city = 'Pune' OR city = 'Delhi' AND signup_date >= '2025-03-01';+------------+-------+-------------+ | name | city | signup_date | +------------+-------+-------------+ | Asha Verma | Pune | 2025-01-15 | | Zoya Khan | Delhi | 2025-04-02 | | Vikram Das | Pune | 2025-07-30 | +------------+-------+-------------+
SELECT name, city, signup_date FROM customers
WHERE (city = 'Pune' OR city = 'Delhi') AND signup_date >= '2025-03-01';+------------+-------+-------------+ | name | city | signup_date | +------------+-------+-------------+ | Zoya Khan | Delhi | 2025-04-02 | | Vikram Das | Pune | 2025-07-30 | +------------+-------+-------------+
IN and BETWEEN
SELECT order_id, status
FROM orders
WHERE status IN ('pending', 'shipped'); -- same as status = 'pending' OR status = 'shipped'+----------+---------+ | order_id | status | +----------+---------+ | 106 | shipped | | 108 | pending | | 109 | shipped | | 111 | pending | +----------+---------+
SELECT name, price
FROM products
WHERE price BETWEEN 450 AND 1299; -- inclusive: 450 and 1299 are included+------------------+---------+ | name | price | +------------------+---------+ | Wireless Mouse | 599.00 | | USB-C Hub | 1299.00 | | Clean Code | 450.00 | | Desk Lamp | 899.00 | | JavaScript Guide | 550.00 | +------------------+---------+
BETWEEN with dates and times
BETWEEN includes both ends. For DATETIME columns, BETWEEN '2025-10-01' AND '2025-10-31' misses everything after midnight on the 31st. Professionals write a half-open range instead: created_at >= '2025-10-01' AND created_at < '2025-11-01'.
LIKE: pattern matching
% matches any number of characters (including none); _ matches exactly one.
SELECT name FROM products WHERE name LIKE '%book%'; -- contains "book"
SELECT name FROM products WHERE name LIKE 'sql%'; -- starts with "sql" (any case)
SELECT name FROM customers WHERE name LIKE '_a%'; -- second letter is "a"
SELECT email FROM customers WHERE email LIKE 'a%@example.com';+-------------+ | name | +-------------+ | A5 Notebook | +-------------+ +-------------------+ | name | +-------------------+ | SQL in 10 Minutes | +-------------------+ +------------+ | name | +------------+ | Ravi Kumar | +------------+ +-------------------+ | email | +-------------------+ | arjun@example.com | | asha@example.com | +-------------------+
With MySQL's default collation, LIKE is case-insensitive, so 'sql%' matched "SQL in 10 Minutes". A pattern starting with % can't use an index and scans the whole table – fine for small tables, slow for millions of rows (use full-text search there).
NULL: the value that isn't a value
NULL means "unknown" or "missing". Any comparison with NULL – even NULL = NULL – gives NULL (unknown), not true, so the row is filtered out. Use IS NULL and IS NOT NULL:
SELECT COUNT(*) AS matches FROM customers WHERE city = NULL; -- always 0!+---------+ | matches | +---------+ | 0 | +---------+
SELECT name, city FROM customers WHERE city IS NULL;+------------+------+ | name | city | +------------+------+ | Neha Gupta | NULL | +------------+------+
NULL also changes the meaning of "not equal". Customers not in Delhi – does Neha (city unknown) count?
SELECT COUNT(*) AS not_delhi FROM customers WHERE city <> 'Delhi';
SELECT COUNT(*) AS not_delhi_or_unknown FROM customers WHERE city <> 'Delhi' OR city IS NULL;+-----------+ | not_delhi | +-----------+ | 5 | +-----------+ +----------------------+ | not_delhi_or_unknown | +----------------------+ | 6 | +----------------------+
Three-valued logic: comparisons can be TRUE, FALSE or UNKNOWN
- 'Pune' = 'Pune'TRUE → kept
- 'Pune' = 'Delhi'FALSE → removed
- NULL = 'Delhi'UNKNOWN → removed
- NULL <> 'Delhi'UNKNOWN → removed
- NULL IS NULLTRUE → kept
The NOT IN trap
WHERE x NOT IN (1, 2, NULL) returns no rows at all, because "x <> NULL" is unknown for every row. This bites when the list comes from a subquery with NULLs – chapter 6 shows the safe alternative, NOT EXISTS.
Practice
Try it yourself
Find Engineering or HR employees earning at least ₹90,000.
Show solution
SELECT name, department, salary FROM employees WHERE department IN ('Engineering', 'HR') AND salary >= 90000;+--------------+-------------+--------+ | name | department | salary | +--------------+-------------+--------+ | Rohit Mehta | Engineering | 180000 | | Sana Iqbal | Engineering | 120000 | | Dev Patel | Engineering | 120000 | | Priya Sharma | Engineering | 95000 | | Kavya Reddy | HR | 90000 | +--------------+-------------+--------+
Find products that are out of stock or have no category.
Show solution
SELECT name, category_id, stock FROM products WHERE stock = 0 OR category_id IS NULL;+-----------+-------------+-------+ | name | category_id | stock | +-----------+-------------+-------+ | USB-C Hub | 1 | 0 | | Desk Lamp | NULL | 15 | +-----------+-------------+-------+
Quick check
Does BETWEEN 10 AND 20 include 10 and 20?
Yes, both ends are included.
Why does WHERE city = NULL return nothing?
Comparing with NULL gives UNKNOWN, never TRUE. Use IS NULL.
What does LIKE 'S_n%' match?
Values starting with S, any one character, then n, then anything – e.g. "Sana", "Sunil".
Summary
- WHERE keeps rows whose condition is TRUE; combine conditions with AND/OR and use parentheses.
- IN lists values, BETWEEN is inclusive, LIKE matches patterns with % and _.
- NULL is unknown: use IS NULL / IS NOT NULL, and remember it fails every = and <> test.
Finished this lesson?
Mark it complete to track your progress, then carry on with the next lesson.