A colleague says HAVING is always slower than WHERE. Is that right, and when does the difference actually matter?
No, that is not right. The two clauses are not a slow version and a fast version of the same thing. They do different jobs, and only one of them can do each job.
So when is HAVING the right choice? Only when the condition is about the group rather than about the row. A condition such as SUM(amount) > 1000 cannot be written in the WHERE clause at all, because the sum does not exist until the rows have been put into groups.
And when is the difference in speed actually real? Only when the condition can be moved into the WHERE clause without changing the answer. And that happens in one case, which is when the condition is on a column of the table rather than on a calculation.
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL,
status VARCHAR(20) NOT NULL,
amount DECIMAL(10, 2) NOT NULL
);
So here is the case. This condition says "the groups whose status is paid".
-- The slow spelling. The filter runs after the grouping.
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING status = 'paid';
-- The fast spelling. The filter runs before the grouping.
SELECT customer_id, COUNT(*) AS order_count
FROM orders
WHERE status = 'paid'
GROUP BY customer_id;
And both of them give the same answer, because the status is the same in every row of a group when the status is one of the grouping columns. So the fast one is strictly better.
So the rule to remember is this. The claim that HAVING is always slower is wrong. The condition belongs in the WHERE clause when it is about a row, and in the HAVING clause when it is about a calculation over a group. And when the condition could go in either place, put it in the WHERE clause.
The next two questions in this topic show the two kinds of rewrite.