WHERE vs HAVING

Question

Why is it that you need to place columns you create yourself (for example "select 1 as number") after HAVING and not WHERE in MySQL? And are there any downsides instead of doing "WHERE 1" (writing the whole definition instead of a column name)

Answer 1

Why is it that you need to place columns you create yourself (for example "select 1 as number") after HAVING and not WHERE in MySQL?

WHERE is applied before GROUP BY, HAVING is applied after (and can filter on aggregates).

In general, you can reference aliases in neither of these clauses, but MySQL allows referencing SELECT level aliases in GROUP BY, ORDER BY and HAVING.

And are there any downsides instead of doing "WHERE 1" (writing the whole definition instead of a column name)

If your calculated expression does not contain any aggregates, putting it into the WHERE clause will most probably be more efficient.

Answer 2

The main difference is that WHERE cannot be used on grouped item (such as SUM(number) whereas HAVING can.

The reason is the Where is done BEFORE the grouping and having is done AFTER the grouping is done.

Answer 3

HAVING is used to filter on aggregations in your GROUP BY.

For example, to check for duplicate names:

SELECT Name FROM Usernames
GROUP BY Name
HAVING COUNT(*) > 1