MySQL WHERE Clause

Last updated: October 19, 2026Oct 19, 2026 5 min read

The WHERE clause filters rows. It contains a condition, and only rows for which the condition is TRUE are returned, updated or deleted. Almost every real query has one, so mastering WHERE is essential.

1. How WHERE Works

SELECT columns FROM table WHERE condition;
UPDATE table SET ... WHERE condition;
DELETE FROM table WHERE condition;

Table: students (7 rows)

2. Comparison Operators

SELECT name, course, fee_paid FROM students WHERE fee_paid >= 40000;

Result: 3 rows

SELECT name, city FROM students WHERE city <> 'Pune';

Result: 3 rows

3. Combining Conditions: AND, OR, NOT

SELECT name, city, age FROM students WHERE city = 'Pune' AND age >= 25;

Result: 2 rows

SELECT name, course FROM students WHERE course = 'Python' OR city = 'Mumbai';

Result: 3 rows

AND is evaluated before OR, so use parentheses to say what you mean:

SELECT name, city, course FROM students
WHERE (city = 'Pune' OR city = 'Delhi') AND course = 'Data Science';

Result: 2 rows

Details: AND, OR, NOT, AND with OR.

4. IN, BETWEEN and LIKE

SELECT name, course FROM students WHERE course IN ('Java', 'Python');

Result: 5 rows

SELECT name, age FROM students WHERE age BETWEEN 25 AND 30;

Result: 2 rows

SELECT name FROM students WHERE name LIKE '%a';   -- names ending in 'a'

Result: 2 rows

5. NULL in WHERE

Deepa's age is unknown (NULL). A comparison with NULL is never TRUE, so neither of these finds her:

SELECT name FROM students WHERE age = NULL;

Result: Empty set (0 rows)

SELECT COUNT(*) AS rows_found FROM students WHERE age > 20 OR age <= 20;

Result: 1 row

Seven students, but only six counted: NULL is neither above nor below 20. Use IS NULL:

SELECT name FROM students WHERE age IS NULL;

Result: 1 row

6. Filtering Dates

SELECT name, joined FROM students WHERE joined >= '2026-01-01';

Result: 3 rows

SELECT name, joined FROM students
WHERE joined >= '2025-08-01' AND joined < '2025-12-01';   -- Aug to Nov 2025

Result: 3 rows

7. Expressions and Functions in WHERE

SELECT name, fee_paid FROM students WHERE fee_paid / 1000 > 45;

Result: 2 rows

SELECT name, city FROM students WHERE UPPER(LEFT(name, 1)) IN ('A', 'E', 'I', 'O', 'U');

Result: 2 rows

8. Sub-queries in WHERE

SELECT name, course, fee_paid
FROM students
WHERE fee_paid > (SELECT AVG(fee_paid) FROM students);

Result: 5 rows

SELECT name, city FROM students
WHERE city IN (SELECT city FROM students GROUP BY city HAVING COUNT(*) >= 2);

Result: 6 rows

9. WHERE in UPDATE and DELETE

The same conditions choose which rows change. Preview with SELECT, then run the change:

SELECT name, fee_paid FROM students WHERE fee_paid = 0;
UPDATE students SET fee_paid = 15000 WHERE fee_paid = 0;
SELECT name, fee_paid FROM students WHERE id = 6;

Preview

After the UPDATE

10. Making WHERE Fast with Indexes

An index on a filtered column lets MySQL jump straight to matching rows. The condition must use the column directly (a sargable condition):

CREATE INDEX idx_joined ON students (joined);
EXPLAIN SELECT name FROM students WHERE joined >= '2026-01-01';
EXPLAIN SELECT name FROM students WHERE YEAR(joined) = 2026;

Bare column: uses the index (range)

Function on the column: full scan (ALL)

11. Practice Exercises

SELECT name, course, fee_paid FROM students
WHERE course IN ('Java', 'Data Science') AND fee_paid >= 45000;

SELECT name, city, age FROM students WHERE city <> 'Pune' AND age IS NOT NULL;

12. Summary

• WHERE keeps rows whose condition is TRUE; FALSE and NULL rows are dropped.
• Use comparison operators, AND/OR/NOT (with parentheses), IN, BETWEEN, LIKE and IS NULL.
• Never test NULL with =; use IS NULL.
• Keep indexed columns bare in conditions so indexes can be used.
• The same WHERE works in SELECT, UPDATE and DELETE.