Search code examples
mysqlsqlwhere-clausehaving-clause

WHERE vs HAVING

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)?

Solution

  • 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.