WHERE vs HAVING

mysql sql where-clause having-clause
baloo picture baloo · May 25, 2010 · Viewed 198.3k times · Source

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

Quassnoi picture Quassnoi · May 25, 2010

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.

Related questions

SELECTING with multiple WHERE conditions on same column

Ok, I think I might be overlooking something obvious/simple here... but I need to write a query that returns only records that match multiple criteria on the same column... My table is a very simple linking setup for applying …

Read More
SQL WHERE condition is not equal to?

Is it possible to negate a where clause? e.g. DELETE * FROM table WHERE id != 2;

Read More
Detect if value is number in MySQL

Is there a way to detect if a value is a number in a MySQL query? Such as SELECT * FROM myTable WHERE isANumber(col1) = true

Read More