WHERE
WHERE filters rows before grouping, keeps only rows whose condition is TRUE, and cannot see aggregates or select-list aliases.
WHERE where_conditionWHERE tests each candidate row and keeps it only when the condition evaluates to TRUE. FALSE drops the row and so does NULL, which is where most surprises in this clause come from.
Before the grouping, not after
WHERE runs before GROUP BY, so it sees individual rows and cannot contain an aggregate function: WHERE COUNT(*) > 3 is an error. HAVING is the clause that filters groups, and the manual notes it is applied nearly last, just before rows are sent to the client, with no optimization. Put every condition that can be expressed per row in WHERE. A row-level condition written in HAVING still returns the right answer, but the server built every group first and then threw most of them away, and no index helped. The manual states it plainly: do not use HAVING for items that should be in the WHERE clause.
Aliases and NULL
WHERE cannot reference a select-list alias, because the column value may not yet have been determined when WHERE is evaluated. Repeat the expression, or wrap the query in a derived table. And no arithmetic comparison matches NULL: status <> 'paid' is NULL, not TRUE, for a row whose status is NULL, so that row silently disappears. Use IS NULL and IS NOT NULL.
Example
SELECT o.id, o.total, o.placed_at
FROM orders AS o
WHERE o.status = 'paid' -- equality: the most index-friendly test
AND o.placed_at >= '2026-09-01' -- half-open range keeps the index usable
AND o.placed_at < '2026-10-01' -- ... and avoids the last-second-of-month bug
AND o.cancelled_at IS NULL -- NOT "= NULL", which matches nothing
AND o.total > 50.00
ORDER BY o.placed_at DESC;When to use it
- Restricting a report to one date range without disabling the index on the date column.
- Excluding soft-deleted rows without accidentally excluding rows whose flag is NULL.
- Moving a slow HAVING condition into WHERE after EXPLAIN shows a full scan.
More examples
WHERE and HAVING in one query
The division of labour to memorise: anything you can decide by looking at a single row goes in WHERE, anything that needs the whole group goes in HAVING. Moving the status filter into HAVING would give the same numbers and read far more rows.
SELECT customer_id,
COUNT(*) AS order_count,
SUM(total) AS spend
FROM orders
WHERE placed_at >= '2026-01-01' -- per-row tests: cheap, run first, use indexes
AND status = 'paid'
GROUP BY customer_id
HAVING COUNT(*) >= 3 -- per-group tests: only these need HAVING
AND SUM(total) > 500
ORDER BY spend DESC;Getting a computed value past WHERE
Reach for option 2 when the expression is long or appears more than once; option 1 when it is short and you want the optimizer to see the raw columns. Neither is faster by definition — check with EXPLAIN.
-- ERROR 1054 (42S22): Unknown column 'gross' in 'where clause'
-- SELECT id, total * 1.21 AS gross FROM orders WHERE gross > 100;
-- Option 1: repeat the expression. Simple, but now it lives in two places.
SELECT id, total * 1.21 AS gross FROM orders WHERE total * 1.21 > 100;
-- Option 2: wrap it, so gross is a real column of the derived table.
SELECT o.id, o.gross
FROM (SELECT id, total * 1.21 AS gross FROM orders) AS o
WHERE o.gross > 100;Writing a filter the index can use
The single highest-value habit in WHERE clauses. Any time a query is slow and the filter column is wrapped in a function, unwrapping it is the first thing to try.
-- Wrapping the column in a function hides it from the index on placed_at:
-- every row must be read and converted before the comparison can happen.
SELECT * FROM orders WHERE YEAR(placed_at) = 2026;
-- The same filter expressed as a range on the bare column. The index on
-- placed_at now supports a range scan.
SELECT * FROM orders
WHERE placed_at >= '2026-01-01'
AND placed_at < '2027-01-01';
-- Same trap, string edition: LEFT(email, 4) = 'ada@' cannot use an index,
-- while a prefix LIKE can.
SELECT * FROM customers WHERE email LIKE 'ada@%';
Discussion