WHERE — Filtering
Filter query results with the SQL WHERE clause — use conditions and comparison and logical operators to fetch exactly the rows you want.
What you will learn
- Filter rows with WHERE
- Use comparison and logical operators
WHERE filters rows
A plain SELECT returns every row. Most of the time you only want some rows — users older than 22, orders from today, products in stock. The WHERE clause adds a condition: MySQL keeps only the rows where the condition is true and throws the rest away. You write it after the table name: SELECT ... FROM table WHERE condition.
Note: Some examples below filter on a city column. Our original users table did not have one, so to try these yourself first add the column and put a city in each row:
ALTER TABLE users ADD city VARCHAR(100);
UPDATE users SET city = 'Mumbai' WHERE id = 1;
UPDATE users SET city = 'Bengaluru' WHERE id = 2;
UPDATE users SET city = 'Mumbai' WHERE id = 3;
ALTER TABLE ... ADD adds a new column to a table that already exists, and the three UPDATE lines fill in a city for each user.
SELECT * FROM users WHERE age > 22;
SELECT * FROM users WHERE city = 'Bengaluru';
SELECT * FROM users WHERE age >= 18 AND city = 'Mumbai';
SELECT * FROM users WHERE age < 20 OR age > 60;Reading each line: the first keeps only rows where age is greater than 22. The second keeps rows where city is exactly Bengaluru (text goes in single quotes). The third uses AND — both conditions must be true (18-or-older and in Mumbai). The fourth uses OR — either condition is enough (younger than 20 or older than 60).
Note: Output (for SELECT * FROM users WHERE age > 22; against our 3 users):
id name email age
2 Ravi ravi@x.com 25
3 Meera meera@x.com 23
Asha (age 22) is left out because 22 is not greater than 22. Only Ravi and Meera satisfy age > 22, so only those two rows come back.
| Operator | Means |
|---|---|
= | Equal |
!= / <> | Not equal |
> < >= <= | Comparisons |
AND / OR | Combine conditions |
Watch out: In SQL, equality is a single = (not ==). And text values go in single quotes: WHERE name = 'Asha'.
Q. Which clause filters which rows a query returns?
✍️ Practice
- Find all users older than 21.
- Find users in a specific city using AND with another condition.
🏠 Homework
- Find all in-stock products under a certain price.