Without WHERE, you get everything. With WHERE, you get exactly what you need. It is the difference between searching "all users" and searching "the user who just placed an order five minutes ago." As a tester, precision matters.
| Operator | Meaning | Example |
|---|---|---|
| = | Equal to | status = 'active' |
| != or <> | Not equal to | status != 'deleted' |
| > | Greater than | balance > 1000 |
| < | Less than | age < 18 |
| >= | Greater than or equal | order_count >= 5 |
| <= | Less than or equal | amount <= 500.00 |
-- AND: Both conditions must be true
SELECT * FROM users
WHERE status = 'active' AND role = 'customer';
-- OR: At least one condition must be true
SELECT * FROM orders
WHERE status = 'failed' OR status = 'cancelled';
-- NOT: Negate a condition
SELECT * FROM users
WHERE NOT status = 'deleted';
-- Combining AND + OR (use parentheses!)
SELECT * FROM orders
WHERE status = 'completed'
AND (payment_method = 'credit_card' OR payment_method = 'debit_card');AND/OR precedence trap: AND is evaluated before OR. Without parentheses, "A OR B AND C" means "A OR (B AND C)", not "(A OR B) AND C". Always use parentheses when mixing AND and OR.
-- IN: Matches any value in a list (cleaner than multiple ORs)
SELECT * FROM orders
WHERE status IN ('pending', 'processing', 'shipped');
-- BETWEEN: Range check (inclusive on both ends)
SELECT * FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-03-31';
-- LIKE: Pattern matching
SELECT * FROM users WHERE email LIKE '%@gmail.com'; -- ends with @gmail.com
SELECT * FROM users WHERE first_name LIKE 'Ra%'; -- starts with "Ra"
SELECT * FROM users WHERE phone LIKE '____'; -- exactly 4 characters
-- IS NULL / IS NOT NULL
SELECT * FROM users WHERE last_login IS NULL; -- never logged in
SELECT * FROM users WHERE phone IS NOT NULL; -- have phone numberYou cannot use = NULL. It will not work. NULL is not a value — it is the absence of a value. Always use IS NULL or IS NOT NULL. This trips up even experienced developers.
Q: What is the difference between WHERE and HAVING?
A: WHERE filters individual rows before grouping. HAVING filters groups after aggregation. Example: WHERE amount > 100 filters rows with amount over 100. HAVING COUNT(*) > 5 filters groups that have more than 5 rows. You cannot use aggregate functions in WHERE — that is what HAVING is for.
Key Point: WHERE filters rows. Use AND/OR with parentheses, IN for lists, BETWEEN for ranges, LIKE for patterns, IS NULL for missing values.