Lesson 4 • Beginner
WHERE Clause & Filtering
Filter your queries to retrieve exactly the rows you need using comparison operators, logical operators, and pattern matching.
🎯 What You'll Learn
- Filter with comparison operators: =, !=, >, <, >=, <=
- Combine conditions with AND, OR, NOT
- Use BETWEEN, IN, LIKE for advanced filtering
- Handle NULL values with IS NULL / IS NOT NULL
Comparison Operators
The WHERE clause filters rows that match a condition. Think of it as a bouncer at a club — only rows that pass the check get through.
| Operator | Meaning | Example |
|---|---|---|
| = | Equal to | age = 25 |
| != or <> | Not equal | status != 'cancelled' |
| > | Greater than | price > 100 |
| < | Less than | stock < 10 |
| >= | Greater or equal | rating >= 4.0 |
| <= | Less or equal | salary <= 50000 |
Comparison Operators
Filter rows using =, >, <, >=, <= and !=
-- Comparison operators
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price <= 50;
SELECT * FROM employees WHERE department = 'Engineering';
SELECT * FROM orders WHERE status != 'cancelled';
-- Numeric comparisons
SELECT name, salary FROM employees
WHERE salary >= 60000;Logical Operators: AND, OR, NOT
Combine multiple conditions to build precise filters:
AND — Both must be true
"Electronics AND under $500" → only cheap electronics
OR — Either can be true
"IT OR Marketing" → employees in either department
NOT — Reverses a condition
"NOT Sales" → everyone except Sales
⚠️ Common Mistake
Forgetting parentheses when mixing AND and OR. AND has higher priority than OR, so A OR B AND C means A OR (B AND C) — probably not what you intended!
AND, OR, NOT
Combine multiple conditions with logical operators
-- AND: ALL conditions must be true
SELECT * FROM products
WHERE category = 'Electronics' AND price < 500;
-- OR: ANY condition can be true
SELECT * FROM students
WHERE grade >= 3.5 OR attendance > 90;
-- NOT: reverse a condition
SELECT * FROM employees
WHERE NOT department = 'Sales';
-- Combine them (use parentheses!)
SELECT * FROM products
WHERE (category = 'Electronics' OR category = 'Books')
AND price < 100;Special Operators: BETWEEN, IN, LIKE, IS NULL
SQL provides shorthand operators that make common patterns cleaner:
| Operator | Instead of | Use |
|---|---|---|
| BETWEEN | x >= 50 AND x <= 200 | x BETWEEN 50 AND 200 |
| IN | x='A' OR x='B' OR x='C' | x IN ('A','B','C') |
| LIKE | Complex string matching | name LIKE 'J%' |
| IS NULL | Can't use = NULL! | phone IS NULL |
💡 Pro Tip
Never write WHERE column = NULL. NULL means "unknown" — you can't compare it with =. Always use IS NULL or IS NOT NULL.
BETWEEN, IN, LIKE, IS NULL
Use special operators for cleaner, more powerful filtering
-- BETWEEN: range of values (inclusive)
SELECT * FROM products
WHERE price BETWEEN 50 AND 200;
-- IN: match any value in a list
SELECT * FROM employees
WHERE department IN ('IT', 'Marketing', 'HR');
-- LIKE: pattern matching
-- % = any number of characters
-- _ = exactly one character
SELECT * FROM customers WHERE name LIKE 'John%';
SELECT * FROM products WHERE code LIKE 'A__1';
SELECT * FROM customers WHERE email LIKE '%@gmail.com';
-- IS NULL / IS NOT NULL
SELECT * FROM employees WHERE phon
...Real-World Filtering
Build complex filters like a real e-commerce product search
-- Real-world filtering scenario:
-- E-commerce product search
SELECT product_name, price, rating, stock
FROM products
WHERE category IN ('Laptops', 'Tablets')
AND price BETWEEN 300 AND 1500
AND rating >= 4.0
AND stock > 0
AND product_name LIKE '%Pro%';
-- Find inactive customers
SELECT first_name, last_name, last_order_date
FROM customers
WHERE last_order_date < '2024-01-01'
OR last_order_date IS NULL;📘 Quick Reference
| Pattern | What it matches |
|---|---|
| 'John%' | Starts with "John" |
| '%son' | Ends with "son" |
| '%an%' | Contains "an" anywhere |
| 'A__' | Starts with A, exactly 3 chars total |
| '_r%' | Second character is "r" |
🎉 Lesson Complete!
You can now filter data with precision using WHERE, logical operators, and special operators. Next, you'll learn to sort results with ORDER BY!
Sign up for free to track which lessons you've completed and get learning reminders.