MySQL – WHERE | Love Me Some Coding

WHERE decides which rows come back. It runs before SELECT, over every row the FROM clause produced, and keeps the ones for which its condition is true — a distinction that matters more than it sounds, because in SQL a condition can also be unknown, and unknown is not kept.

Comparisons

The operators are the ones you would guess: =, <> (also written !=), <, <=, >, >=.

SELECT name, type
FROM   product
WHERE  type = 'DRINK'
ORDER  BY id;

+---------------+-------+
| name          | type  |
+---------------+-------+
| Pepsi         | DRINK |
| Diet Pepsi    | DRINK |
| Mountain Dew  | DRINK |
| Starry        | DRINK |
| Bottled Water | DRINK |
| Iced Tea      | DRINK |
+---------------+-------+

String comparison is case-insensitive here, because the column's collation is utf8mb4_0900_ai_ci — the ci is "case insensitive". So type = 'drink' returns the same six rows. That is a property of the collation, not of MySQL, and it is worth knowing rather than assuming.

AND, OR, and the reason to type parentheses

SELECT customer_name, order_type, total
FROM   customer_order
WHERE  order_type = 'DELIVERY' AND total > 40
ORDER  BY total DESC;

+----------------+------------+-------+
| customer_name  | order_type | total |
+----------------+------------+-------+
| Demo Customer  | DELIVERY   | 58.19 |
| Casey Lindgren | DELIVERY   | 40.86 |
+----------------+------------+-------+

AND binds tighter than OR, exactly like × binds tighter than +. Mixing them without parentheses is the single most common way to write a filter that looks right and is not:

-- reads as: CANCELLED  OR  (PREPARING AND DELIVERY)
SELECT customer_name, order_type, status
FROM   customer_order
WHERE  status = 'CANCELLED' OR status = 'PREPARING' AND order_type = 'DELIVERY'
ORDER  BY id;

+----------------+------------+-----------+
| customer_name  | order_type | status    |
+----------------+------------+-----------+
| Chris Vaughn   | DELIVERY   | CANCELLED |
| Taylor Brooks  | CARRYOUT   | CANCELLED |
| Casey Lindgren | DELIVERY   | PREPARING |
+----------------+------------+-----------+

Taylor Brooks is a carryout order, and the query was meant to be about deliveries. The parentheses are the fix:

SELECT customer_name, order_type, status
FROM   customer_order
WHERE  (status = 'CANCELLED' OR status = 'PREPARING') AND order_type = 'DELIVERY'
ORDER  BY id;

+----------------+------------+-----------+
| customer_name  | order_type | status    |
+----------------+------------+-----------+
| Chris Vaughn   | DELIVERY   | CANCELLED |
| Casey Lindgren | DELIVERY   | PREPARING |
+----------------+------------+-----------+

Nothing warns you. Parenthesise whenever both operators appear.

IN

IN is shorthand for a chain of ORs against the same column, and it is both shorter and harder to get wrong:

SELECT customer_name, status
FROM   customer_order
WHERE  status IN ('CANCELLED', 'PREPARING')
ORDER  BY id;

+----------------+-----------+
| customer_name  | status    |
+----------------+-----------+
| Chris Vaughn   | CANCELLED |
| Taylor Brooks  | CANCELLED |
| Casey Lindgren | PREPARING |
+----------------+-----------+

The list can be a subquery instead of literals. If it is, read the warning about NOT IN and NULLs in that lesson before you use the negation.

The NULL trap

In the pizza schema customer_order.user_id is NULL exactly when the order was placed by a guest. Counting those the obvious way returns nothing at all:

SELECT COUNT(*) AS guest_orders
FROM   customer_order
WHERE  user_id = NULL;

+--------------+
| guest_orders |
+--------------+
|            0 |
+--------------+

There are ten such orders. The query is not wrong about the data — it is asking the wrong question. NULL means unknown, so user_id = NULL evaluates to unknown rather than to true or false, and WHERE keeps only rows that are true. No error, no warning, just an empty answer.

SELECT COUNT(*) AS guest_orders
FROM   customer_order
WHERE  user_id IS NULL;

+--------------+
| guest_orders |
+--------------+
|           10 |
+--------------+

IS NULL and IS NOT NULL are the only operators that test for it. The full story — including how NULL behaves in aggregates and in NOT IN — is in NULL, IS NULL and ISNULL().

Keep the column bare

These two find the same rows:

-- cannot use an index on created_at: every row must be read and converted first
SELECT COUNT(*) FROM customer_order WHERE YEAR(created_at) = 2026;

-- can use one: the range is expressed in terms of the stored column
SELECT COUNT(*) FROM customer_order WHERE created_at >= '2026-01-01'
                                      AND created_at <  '2027-01-01';

Wrapping an indexed column in a function makes the index unusable, because the index stores created_at and the query asks about YEAR(created_at) — a different value, in a different order. The same applies to DATE(col), UPPER(col) and arithmetic like col * 2 > 100. Rearrange so the column sits alone on one side. On 18 rows it makes no measurable difference; on the 400,000-row table in the indexes lesson it is the whole difference.

What to remember

WHERE keeps rows where the condition is true — not merely "not false".
AND binds tighter than OR. Parenthesise when both appear.
= NULL is never true. Use IS NULL.
Keep the indexed column bare on its side of the comparison.

Next: NULL and IS NULL in full.