Chapter 07 of 35

WHERE Clause

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          no

Notice 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 ==)

dept_id = 20

<>

Not equal to (SQL standard)

city <> 'Mumbai'

!=

Not equal to (also accepted by MySQL)

city != 'Mumbai'

>

Greater than

salary > 90000

<

Less than

salary < 60000

>=

Greater than or equal to

salary >= 95000

<=

Less than or equal to

amount <= 1000

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. The utf8mb4_0900_* collations are "NO PAD" collations, so trailing spaces are significant. (Older collations such as utf8mb4_general_ci are "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 = 1 is used by programs that build queries dynamically: every extra filter can then be appended as AND condition without checking whether it is the first one.

  • CREATE TABLE new_t AS SELECT * FROM old_t WHERE 1 = 0 copies 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

WHERE city = Mumbai

Error 1054: Unknown column

WHERE city = 'Mumbai'

WHERE dept_id == 20

Syntax error

SQL uses a single =

WHERE email = NULL

Always empty

WHERE email IS NULL

Using a SELECT alias in WHERE

Error 1054

Repeat the expression or use a subquery

Expecting <> to return NULL rows

NULL rows silently missing

Add OR col IS NULL

Comparing a text column with a number

Text converted to numbers, wrong matches

Quote string values

Expecting case-sensitive matching in MySQL

'mumbai' matches 'Mumbai'

COLLATE utf8mb4_bin

Dates as '15-01-2024'

Error 1525: Incorrect DATE value

'2024-01-15'


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

  • WHERE keeps 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_ci collation 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 = NULL and <>.

  • 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.