Filtering with WHERE
Use the WHERE clause to filter rows with operators, AND, OR, IN, BETWEEN, and LIKE.
What you'll learn
- How the WHERE clause filters rows before they are returned
- Comparison operators: =, !=, <, >, <=, >=
- Combining conditions with AND, OR, and NOT
- Using IN, BETWEEN, and LIKE for set and pattern matching
- Filtering on NULL with IS NULL and IS NOT NULL
Concept
The WHERE Clause
WHERE goes after FROM and before ORDER BY / LIMIT. It evaluates a condition for every row and returns only the rows where the condition is TRUE. This filtering happens on the server before data is sent to your application, so it is far more efficient than fetching all rows and filtering in application code.
Comparison Operators
| Operator | Meaning | Example | |----------|---------|---------| | = | Equal | price = 19.99 | | != or <> | Not equal | status != 'deleted' | | <, > | Less / greater | price < 50 | | <=, >= | Less or equal / greater or equal | stock >= 10 |
Combining Conditions
Use AND (both must be true), OR (at least one must be true), and NOT (negate). Use parentheses to control precedence — AND binds tighter than OR, so a OR b AND c means a OR (b AND c). Always use parentheses when mixing AND and OR to make intent explicit.
IN, BETWEEN, LIKE, IS NULL
IN (v1, v2, ...)— column matches any value in the listBETWEEN x AND y— inclusive range checkLIKE 'pat%'— pattern matching;%matches any sequence,_matches one charIS NULL/IS NOT NULL— you cannot use= NULL; NULL is not equal to anything, even itself
Example
-- Products in specific categories, priced between 10 and 100
SELECT name, price, category
FROM products
WHERE category IN ('electronics', 'books', 'toys')
AND price BETWEEN 10 AND 100
AND in_stock = TRUE
ORDER BY price ASC;
-- Find users whose name starts with 'A' and have no email set
SELECT id, name
FROM users
WHERE name LIKE 'A%'
AND email IS NULL;
-- Complex OR with parentheses
SELECT * FROM orders
WHERE (status = 'shipped' AND total > 100)
OR (status = 'pending' AND total > 500);
Three patterns: IN + BETWEEN for range and set filtering, LIKE + IS NULL for pattern and null checks, and parenthesized OR for complex logic.
Try it
- SQL Formatter
Format complex WHERE clauses with multiple conditions.
- Regex Tester
LIKE patterns are simpler than regex — compare the two.
Common mistakes
The mistake
Using = NULL instead of IS NULL
The fix
NULL = NULL evaluates to NULL (not TRUE) in SQL. Always use IS NULL or IS NOT NULL to test for null values.
The mistake
Forgetting parentheses when mixing AND and OR
The fix
AND binds tighter than OR. Always use parentheses: (a OR b) AND c, not a OR b AND c, unless you are certain of the precedence.
Practice Filtering with WHERE
14 exercises
Look up unfamiliar terms
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
Build real projects
Apply what you learned by building guided projects with starter code and solutions.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.