WHERE vs HAVING, and when the choice changes your answer

WHERE filters rows before they're grouped. HAVING filters groups after they're built. That's the entire difference, and it explains the behavior below.

Logical order of a query:

FROM / JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT

An aggregate like SUM(salary) doesn't exist yet when WHERE runs, which is why this is an error:

SELECT dept, AVG(salary) FROM employees WHERE AVG(salary) > 100000 GROUP BY dept;
-- misuse of aggregate function

HAVING is where that condition goes:

SELECT dept, ROUND(AVG(salary)) AS avg_salary, COUNT(*) AS people
FROM employees
GROUP BY dept
HAVING AVG(salary) > 100000;

When the choice changes the result

These two ask different questions. The first averages only the people earning over 100,000, then reports each department. The second averages everybody, then keeps departments that clear the bar.

SELECT dept, ROUND(AVG(salary)) AS avg_salary
FROM employees
WHERE salary > 100000
GROUP BY dept;

SELECT dept, ROUND(AVG(salary)) AS avg_salary
FROM employees
GROUP BY dept
HAVING AVG(salary) > 100000;

Look for sales. It appears in the first result, with an average of one person's salary, because everyone earning under 100,000 was removed before the average was taken. It's missing from the second, because the department's real average is below the bar. The first version is rarely what the person asking meant.

When they're interchangeable

A condition on a grouping column gives the same result in either place:

SELECT dept, COUNT(*) AS people FROM employees WHERE dept <> 'exec' GROUP BY dept;

Prefer WHERE here. It discards rows before the expensive grouping step, and it tells the reader the rows were never part of the question. Many optimizers will move such a HAVING condition down for you, but you shouldn't rely on it.

Using both

Most real reports do. This one finds categories with more than 100 units sold in shipped orders. The row filter on status goes in WHERE, and the condition on the total goes in HAVING.

SELECT p.category, SUM(i.qty) AS units
FROM order_items i
JOIN orders o ON o.id = i.order_id
JOIN products p ON p.id = i.product_id
WHERE o.status = 'shipped'
GROUP BY p.category
HAVING SUM(i.qty) > 100
ORDER BY units DESC;