In the SELECT Statement chapter, every query returned every row of the table. That is fine for a 10-row table, but a real orders table can have crores of rows, and you almost never want all of them. You want "orders of customer 101", "employees earning more than 90,000", or "orders placed this month".
The WHERE clause is how we tell SQL which rows we want. In this chapter we will learn how it works, the comparison operators, and how to compare numbers, strings and dates correctly in MySQL, including some surprising behaviours that catch even experienced developers.
All examples use the shared company_db sample database, and every output shown is real MySQL 8.4 output.
What is the WHERE Clause?
The WHERE clause filters rows. It contains a condition, and only the rows for which the condition is TRUE are included in the result.
SELECT column1, column2, ...
FROM table_name
WHERE condition;A first example: all employees in department 20 (Sales).
SELECT emp_id, name, dept_id
FROM employees
WHERE dept_id = 20;+--------+--------------+---------+
| emp_id | name | dept_id |
+--------+--------------+---------+
| 4 | Sneha Kapoor | 20 |
| 5 | Vikram Singh | 20 |
| 6 | Ananya Das | 20 |
+--------+--------------+---------+
3 rows in set (0.00 sec)In relational algebra, picking rows like this is called selection (σ), while picking columns is projection (π). A normal query does both: WHERE picks the rows, the SELECT list picks the columns.
How WHERE Works, Row by Row
Conceptually, MySQL takes the table from FROM and tests the condition against each row, one at a time:
Condition: dept_id = 20
emp_id name dept_id dept_id = 20 ? Kept?
------ ------------- ------- -------------- -----
1 Aarav Sharma 10 FALSE no
2 Priya Iyer 10 FALSE no
3 Rohan Mehta 10 FALSE no
4 Sneha Kapoor 20 TRUE YES
5 Vikram Singh 20 TRUE YES
6 Ananya Das 20 TRUE YES
7 Karan Patel 30 FALSE no
8 Meera Nair 30 FALSE no
9 Arjun Reddy 40 FALSE no
10 Ishita Gupta NULL UNKNOWN noNotice the last row. Ishita has no department (NULL). Comparing NULL = 20 is neither true nor false; it is UNKNOWN. WHERE keeps only TRUE rows, so UNKNOWN rows are dropped just like FALSE ones. Keep this in mind; it explains several "missing rows" bugs later in this chapter, and the NULL Values chapter covers it in full.
Remember the logical processing order from the previous chapter: FROM → WHERE → ... → SELECT. Filtering happens before the SELECT list is computed. That is why the filter can use any column of the table, even one you do not display.
SELECT name FROM employees WHERE emp_id = 7;+-------------+
| name |
+-------------+
| Karan Patel |
+-------------+
1 row in set (0.00 sec)We filtered on emp_id without selecting it.
Comparison Operators
Operator | Meaning | Example |
|---|---|---|
| Equal to (a single |
|
| Not equal to (SQL standard) |
|
| Not equal to (also accepted by MySQL) |
|
| Greater than |
|
| Less than |
|
| Greater than or equal to |
|
| Less than or equal to |
|
MySQL also has a special <=> ("null-safe equal") operator, which we will meet in the NULL Values chapter. Operators that test lists, ranges and patterns (IN, BETWEEN, LIKE) and the logical operators (AND, OR, NOT) are the topic of the next chapter.
Filtering Numbers
Employees earning more than 90,000:
SELECT name, salary FROM employees WHERE salary > 90000;+--------------+-----------+
| name | salary |
+--------------+-----------+
| Aarav Sharma | 150000.00 |
| Priya Iyer | 95000.00 |
| Rohan Mehta | 95000.00 |
| Sneha Kapoor | 110000.00 |
+--------------+-----------+
4 rows in set (0.00 sec)Employees earning less than 60,000:
SELECT name, salary FROM employees WHERE salary < 60000;+--------------+----------+
| name | salary |
+--------------+----------+
| Ananya Das | 58000.00 |
| Meera Nair | 48000.00 |
| Ishita Gupta | 55000.00 |
+--------------+----------+
3 rows in set (0.00 sec)Exact match on a number. Numbers are written without quotes:
SELECT name, salary FROM employees WHERE salary = 95000;+-------------+----------+
| name | salary |
+-------------+----------+
| Priya Iyer | 95000.00 |
| Rohan Mehta | 95000.00 |
+-------------+----------+
2 rows in set (0.00 sec)95000 and the stored 95000.00 are equal as numbers, so both rows match. Because salary is an exact DECIMAL type, equality checks are safe. On approximate types like FLOAT/DOUBLE, comparing with = is risky because of rounding (see the SQL Data Types chapter).
> vs >=: the Boundary Matters
On our data, salary > 90000 and salary >= 95000 happen to return the same four people, but they mean different things. If someone earned exactly 90,000, the first query would skip them; the second would also skip them; salary >= 90000 would include them. When a requirement says "at least", "minimum" or "or more", you need >=. Off-by-one boundaries are one of the most common filtering bugs.
Filtering Strings
Text values must be inside single quotes:
SELECT order_id, status FROM orders WHERE status = 'Pending';+----------+---------+
| order_id | status |
+----------+---------+
| 1007 | Pending |
| 1008 | Pending |
+----------+---------+
2 rows in set (0.00 sec)Forgetting the quotes makes MySQL think you are naming a column:
SELECT name, city FROM employees WHERE city = Mumbai;ERROR 1054 (42S22): Unknown column 'Mumbai' in 'where clause'Single quotes = a value ('Mumbai'). Backticks = a name (`city`). In standard SQL, double quotes are also for names, but MySQL by default treats them as strings. Stick to single quotes for values and your SQL will work on every database.
Not Equal
SELECT name, city FROM employees WHERE city <> 'Mumbai';+--------------+-----------+
| name | city |
+--------------+-----------+
| Aarav Sharma | Bengaluru |
| Priya Iyer | Chennai |
| Rohan Mehta | Bengaluru |
| Ananya Das | Kolkata |
| Karan Patel | Delhi |
| Meera Nair | Kochi |
| Arjun Reddy | Hyderabad |
+--------------+-----------+
7 rows in set (0.00 sec)city != 'Mumbai' returns exactly the same 7 rows. Now count: there are 10 employees, 2 live in Mumbai, so "not Mumbai" should be 8, yet we got 7. Ishita Gupta is missing because her city is NULL. NULL <> 'Mumbai' is UNKNOWN, not TRUE, so she is filtered out. The same thing happens with numbers:
SELECT order_id, customer_id FROM orders WHERE customer_id <> 101;+----------+-------------+
| order_id | customer_id |
+----------+-------------+
| 1002 | 102 |
| 1006 | 102 |
| 1004 | 103 |
| 1005 | 104 |
+----------+-------------+
4 rows in set (0.00 sec)Out of 8 orders, 3 belong to customer 101, yet only 4 (not 5) are returned. Guest order 1008 has a NULL customer and disappears. How to include such rows on purpose is shown in the NULL Values chapter.
Case-Insensitive by Default
SELECT name, city FROM employees WHERE city = 'mumbai';+--------------+--------+
| name | city |
+--------------+--------+
| Sneha Kapoor | Mumbai |
| Vikram Singh | Mumbai |
+--------------+--------+
2 rows in set (0.00 sec)The lowercase 'mumbai' matched 'Mumbai'. Why? String comparisons follow the column's collation: a set of rules for comparing and sorting text. MySQL 8's default collation is utf8mb4_0900_ai_ci:
ai= accent-insensitive ('e' equals 'é')ci= case-insensitive ('A' equals 'a')
This is different from PostgreSQL and Oracle, where = on text is case-sensitive by default, so it is a classic interview "gotcha". If you need an exact, case-sensitive match in MySQL, apply a binary collation:
SELECT name, city FROM employees WHERE city COLLATE utf8mb4_bin = 'mumbai';Empty set (0.00 sec)SELECT name, city FROM employees WHERE city COLLATE utf8mb4_bin = 'Mumbai';+--------------+--------+
| name | city |
+--------------+--------+
| Sneha Kapoor | Mumbai |
| Vikram Singh | Mumbai |
+--------------+--------+
2 rows in set (0.00 sec)You may see older code that writes WHERE BINARY city = 'mumbai'. It still works in MySQL 8.4 but raises warning 1287 ("'BINARY expr' is deprecated"), so prefer COLLATE or CAST.
String Comparisons in One Query
SELECT 'Mumbai' = 'mumbai', 'Mumbai' = 'Mumbai ', 'abc' < 'abd', 'Zebra' < 'apple';+---------------------+----------------------+---------------+-------------------+
| 'Mumbai' = 'mumbai' | 'Mumbai' = 'Mumbai ' | 'abc' < 'abd' | 'Zebra' < 'apple' |
+---------------------+----------------------+---------------+-------------------+
| 1 | 0 | 1 | 0 |
+---------------------+----------------------+---------------+-------------------+
1 row in set (0.00 sec)'Mumbai' = 'mumbai'→ 1 (true): case is ignored.'Mumbai' = 'Mumbai '→ 0 (false): the trailing space matters. Theutf8mb4_0900_*collations are "NO PAD" collations, so trailing spaces are significant. (Older collations such asutf8mb4_general_ciare "PAD SPACE" and would say 1 here.) Stray spaces from user input are a real source of "why doesn't my WHERE match?" bugs.'abc' < 'abd'→ 1: strings compare character by character, like a dictionary.'Zebra' < 'apple'→ 0: with a case-insensitive collation, 'Z' is compared as 'z', which comes after 'a'. (In a pure ASCII/binary comparison, uppercase 'Z' (90) is less than 'a' (97), and the answer would be 1.)
Equality Means the Whole Value
SELECT name FROM employees WHERE name = 'Priya';Empty set (0.00 sec)The stored value is 'Priya Iyer', not 'Priya'. = compares the entire string. To match "names starting with Priya" you need LIKE 'Priya%', covered in the next chapter.
Filtering Dates
Dates are written as strings in 'YYYY-MM-DD' format, and MySQL converts them to DATE values for comparison. "Later" means "greater".
SELECT name, hire_date FROM employees WHERE hire_date >= '2021-01-01';+--------------+------------+
| name | hire_date |
+--------------+------------+
| Vikram Singh | 2021-03-08 |
| Ananya Das | 2022-06-30 |
| Meera Nair | 2023-02-14 |
| Ishita Gupta | 2024-01-10 |
+--------------+------------+
4 rows in set (0.00 sec)Orders placed in January 2024 or earlier (before 1 February):
SELECT order_id, order_date, amount FROM orders WHERE order_date < '2024-02-01';+----------+------------+---------+
| order_id | order_date | amount |
+----------+------------+---------+
| 1001 | 2024-01-15 | 2500.00 |
| 1002 | 2024-01-20 | 4800.00 |
+----------+------------+---------+
2 rows in set (0.00 sec)Always use the ISO format 'YYYY-MM-DD'. A format like '15-01-2021' is not understood as a date. MySQL 8.4 rejects the comparison:
SELECT name, hire_date FROM employees WHERE hire_date >= '15-01-2021';ERROR 1525 (HY000): Incorrect DATE value: '15-01-2021'An error is the lucky case; a string that MySQL can partly interpret may be compared in a way you did not intend, so stick to ISO dates. Date ranges with BETWEEN come in the next chapter, and date functions like YEAR() in the Date & Time Functions chapter.
Expressions in WHERE
The condition can contain calculations. Employees whose annual salary exceeds 10 lakh:
SELECT name, salary, salary * 12 AS annual
FROM employees
WHERE salary * 12 > 1000000;+--------------+-----------+------------+
| name | salary | annual |
+--------------+-----------+------------+
| Aarav Sharma | 150000.00 | 1800000.00 |
| Priya Iyer | 95000.00 | 1140000.00 |
| Rohan Mehta | 95000.00 | 1140000.00 |
| Sneha Kapoor | 110000.00 | 1320000.00 |
+--------------+-----------+------------+
4 rows in set (0.00 sec)You Cannot Use a Column Alias in WHERE
It is tempting to write the shorter version:
SELECT name, salary * 12 AS annual
FROM employees
WHERE annual > 1000000;ERROR 1054 (42S22): Unknown column 'annual' in 'where clause'This fails because of the logical order: WHERE is evaluated before the SELECT list, so the alias annual does not exist yet. Repeat the expression in WHERE (as above), or use a subquery or CTE (later chapters).
A Performance Tip: Keep the Column Alone
WHERE salary * 12 > 1000000 is correct, but if salary had an index, MySQL could not use it efficiently, because it would have to compute salary * 12 for every row first. The equivalent WHERE salary > 1000000 / 12 leaves the column "bare" so an index can be used. Conditions that leave the column untouched are called sargable (Search ARGument-able). We will return to this in the Indexes chapter.
Type Conversion Surprises
When you compare values of different types, MySQL silently converts one of them. This is convenient, but it can hide bugs.
Comparing a number column with a quoted number works fine:
SELECT name FROM employees WHERE emp_id = '7';+-------------+
| name |
+-------------+
| Karan Patel |
+-------------+
1 row in set (0.00 sec)But look at what happens with garbage after the number:
SELECT name FROM employees WHERE emp_id = '7abc';
SHOW WARNINGS;+-------------+
| name |
+-------------+
| Karan Patel |
+-------------+
1 row in set, 1 warning (0.00 sec)
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '7abc' |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)MySQL converted '7abc' to the number 7 by reading the leading digits, and only gave a warning. The reverse is even more dangerous. Comparing a text column with a number converts every text value to a number, and text that does not start with a digit becomes 0:
SELECT name, city FROM employees WHERE city = 0;+--------------+-----------+
| name | city |
+--------------+-----------+
| Aarav Sharma | Bengaluru |
| Priya Iyer | Chennai |
| Rohan Mehta | Bengaluru |
| Sneha Kapoor | Mumbai |
| Vikram Singh | Mumbai |
| Ananya Das | Kolkata |
| Karan Patel | Delhi |
| Meera Nair | Kochi |
| Arjun Reddy | Hyderabad |
+--------------+-----------+
9 rows in set, 9 warnings (0.00 sec)Every city "equals" 0. (Ishita's NULL city is still excluded, for the reason we saw earlier.) The lesson: always compare a column with a value of the same type. Numbers without quotes for numeric columns, quoted strings for text columns, and check SHOW WARNINGS when MySQL reports warnings.
When Nothing Matches
There are no employees in department 50 (Research):
SELECT name, dept_id FROM employees WHERE dept_id = 50;Empty set (0.00 sec)An empty result is not an error. It is a valid answer: "zero rows satisfy this condition".
The = NULL Trap
Meera Nair has no email. A beginner would try:
SELECT name, email FROM employees WHERE email = NULL;Empty set (0.00 sec)No error, but no rows either, even though a NULL email definitely exists. anything = NULL is always UNKNOWN, never TRUE. The correct test is email IS NULL, explained in the NULL Values chapter.
Always-True and Always-False Conditions
The condition does not have to mention a column:
SELECT COUNT(*) FROM employees WHERE 1 = 0;+----------+
| COUNT(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)WHERE 1 = 0 is never true, so no rows pass. WHERE 1 = 1 is always true and returns all 10 employees. Why would anyone write these?
WHERE 1 = 1is used by programs that build queries dynamically: every extra filter can then be appended asAND conditionwithout checking whether it is the first one.CREATE TABLE new_t AS SELECT * FROM old_t WHERE 1 = 0copies a table's column structure without any rows.In security,
' OR 1=1 --is the textbook SQL injection: if user input is pasted into a query string, an attacker can turn the filter into an always-true condition. Always use parameterized queries in application code.
MySQL also treats any non-zero number as true. WHERE salary (with no comparison) returns every employee whose salary is non-zero and non-NULL. It is legal but confusing; always write a full comparison.
WHERE is Not Only for SELECT
The same WHERE clause controls which rows are changed by UPDATE and removed by DELETE:
UPDATE employees SET salary = salary * 1.10 WHERE dept_id = 20;
DELETE FROM orders WHERE status = 'Cancelled';Without a WHERE, these statements affect every row in the table. A safe habit: before running an UPDATE or DELETE, run a SELECT with the same WHERE and check which rows come back. These statements are covered properly in the UPDATE & DELETE chapter.
Common Mistakes
Mistake | Result | Correct |
|---|---|---|
| Error 1054: Unknown column |
|
| Syntax error | SQL uses a single |
| Always empty |
|
Using a SELECT alias in WHERE | Error 1054 | Repeat the expression or use a subquery |
Expecting | NULL rows silently missing | Add |
Comparing a text column with a number | Text converted to numbers, wrong matches | Quote string values |
Expecting case-sensitive matching in MySQL |
|
|
Dates as | Error 1525: Incorrect DATE value |
|
Interview Questions
What does the WHERE clause do?
It filters rows: the condition is evaluated for each row, and only rows where it is TRUE are kept. Rows where it is FALSE or UNKNOWN (because of NULL) are discarded.
What is the difference between <> and != in MySQL?
None; both mean "not equal". <> is the SQL standard form and works on every database, so it is usually preferred.
Why does WHERE salary * 12 > X work but WHERE annual > X (with annual as alias) fail?
WHERE is logically evaluated before the SELECT list, so aliases defined in SELECT do not exist yet when WHERE runs. Expressions on real columns are fine.
Is string comparison case-sensitive in MySQL?
By default no. MySQL 8's default collation utf8mb4_0900_ai_ci is case- and accent-insensitive. Use a binary or _cs collation (e.g. COLLATE utf8mb4_bin) for case-sensitive comparison.
Why does WHERE city <> 'Mumbai' not return rows where city is NULL?
Any comparison with NULL yields UNKNOWN, and WHERE only keeps TRUE rows. To include them, write WHERE city <> 'Mumbai' OR city IS NULL.
What is a sargable condition?
A condition the optimizer can satisfy using an index, typically with the column on its own: salary > 50000 is sargable; salary * 12 > 600000 or YEAR(hire_date) = 2021 wraps the column in an expression and generally is not.
What is the difference between WHERE and HAVING?
WHERE filters individual rows before grouping; HAVING filters groups after GROUP BY and can use aggregate functions. Covered in the GROUP BY & HAVING chapter.
Summary
WHEREkeeps only rows whose condition is TRUE; FALSE and UNKNOWN rows are dropped.Comparison operators:
=,<>/!=,<,>,<=,>=.Strings and dates go in single quotes; numbers do not. Use
'YYYY-MM-DD'for dates.MySQL's default string comparison is case-insensitive, and with the default
utf8mb4_0900_ai_cicollation trailing spaces count.WHERE runs before SELECT, so it cannot use column aliases.
Mixing types triggers silent conversion; compare like with like.
Comparisons with NULL never match, including
= NULLand<>.The same WHERE controls UPDATE and DELETE; test it with SELECT first.
A single condition is rarely enough. In the next chapter, SQL Operators (AND, OR, IN, BETWEEN, LIKE), we will combine conditions and match lists, ranges and patterns.