WHERE filters individual rows before grouping.
Comparison operators
| Operator | Meaning |
|---|---|
= | Equal |
<> or != | Not equal |
> < >= <= | Greater / less than |
<=> | NULL-safe equal |
SELECT * FROM students WHERE department = 'CSE';
SELECT * FROM students WHERE cgpa >= 3.50;
SELECT * FROM students WHERE age <> 20;
Logical operators
SELECT * FROM students WHERE department = 'CSE' AND cgpa > 3.0;
SELECT * FROM students WHERE department = 'CSE' OR department = 'EEE';
SELECT * FROM students WHERE NOT department = 'CSE';
-- AND binds tighter than OR — use parentheses when mixing
SELECT * FROM students
WHERE (department = 'CSE' OR department = 'EEE') AND cgpa > 3.0;
Range and set
SELECT * FROM students WHERE age BETWEEN 18 AND 22; -- inclusive
SELECT * FROM students WHERE age NOT BETWEEN 18 AND 22;
SELECT * FROM students WHERE department IN ('CSE','EEE','BBA');
SELECT * FROM students WHERE department NOT IN ('CSE');
Pattern matching with LIKE
| Wildcard | Matches |
|---|---|
% | Any number of characters (including none) |
_ | Exactly one character |
SELECT * FROM students WHERE name_en LIKE 'R%'; -- starts with R
SELECT * FROM students WHERE email LIKE '%@gmail.com';
SELECT * FROM students WHERE roll_no LIKE 'CSE-1_1';
SELECT * FROM students WHERE name_en LIKE '%uddin%';
Escape a literal % with ESCAPE: WHERE code LIKE '50\%' ESCAPE '\\'.
For real regular expressions use REGEXP:
SELECT * FROM students WHERE email REGEXP '^[a-z]+[0-9]*@example\\.com$';
NULL handling
NULL means "unknown", so = NULL is never true. Use IS NULL:
SELECT * FROM students WHERE email IS NULL;
SELECT * FROM students WHERE email IS NOT NULL;
SELECT * FROM students WHERE email <=> NULL; -- NULL-safe equality
SELECT name_en, IFNULL(email, 'no email') FROM students;
Performance note
A WHERE clause that wraps a column in a function cannot use an index:
-- slow: index on admitted_on is ignored
WHERE YEAR(admitted_on) = 2024
-- fast: sargable, uses the index
WHERE admitted_on >= '2024-01-01' AND admitted_on < '2025-01-01'