What is the difference between WHERE and HAVING, and why does the order in which they run matter?
The difference is where in the process the filter happens, and that decides what the numbers mean.
WHERE filters the rows before the grouping happens. HAVING filters the groups after the grouping has happened.
Here is the order that the engine works in. The rows come out of the tables. The WHERE clause throws away the rows that do not match. The remaining rows are put into groups by the GROUP BY. The HAVING clause throws away the groups that do not match. Then the SELECT picks the columns, the ORDER BY sorts them, and the limit takes the first few.
So why does the order matter? Because the amount and the count are worked out from the rows that reach the grouping step, and the WHERE clause decides which rows those are.
There is one more difference, and it is about speed. The WHERE clause can use an index, because it is looking at the real columns of the real rows. The HAVING clause usually cannot, because it is looking at a group and at the result of a calculation such as a count or a sum.
-- The WHERE clause runs first, so this counts only the big orders.
SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total
FROM orders
WHERE amount > 100
GROUP BY customer_id;
-- The HAVING clause runs last, so this sums everything and then
-- throws the groups away.
SELECT customer_id, COUNT(*) AS order_count, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 100;
The next question in this topic asks you to write the same report both ways and explain why the numbers differ.