WHERE vs HAVING

Question

Why do 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

To answer the first part of your question: WHERE clause is used before GROUP BY, and HAVING is used after (and can filter on aggregates) it.

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

To answer the second part of your question: If the calculated expression does not contain any aggregates, then putting it into the WHERE clause will most probably be more efficient.