Why do you have "where" when there is "availability",

Question

I know this has been discussed a lot, but none of my research could convince me of the difference between the where and having clauses in MySQL. From what I understand, we can achieve everything that can be done with the where clause using ' having '. E.g. select * from users having username='admin' . Then why do you need a " where " sentence? Does it use any performance differences?

Answer 1

The WHERE filters data from the source before aggregates, while HAVING clusters the data after applying GROUP BY . As a rule, this means that any non-aggregate filter can appear anywhere, but if you have a column that your query does not refer to, you can filter it only in WHERE .

For example, if you have the following table:

 | ID | VALUE | -------------- | 1 | 15 | | 2 | 15 | | 3 | 20 | | 4 | 20 | | 5 | 25 | | 6 | 30 | | 7 | 40 | 

Suppose you wanted to apply the following query:

 select value, count(value) from Table1 group by value 

But you wanted to include lines where ID > 2 . If you put this in the HAVING , you will get an error because the ID column is not an available post aggregate because it is not in the SELECT . In this case, you will need to use the WHERE instead:

 select value, count(value) from Table1 where id > 2 group by value 

Demo: http://www.sqlfiddle.com/#!2/f6741/16

Answer 2

The difference between HAVING from WHERE is that HAVING supports aggregated columns, and WHERE not because it applies only to individual rows., EG

 SELECT ID FROM tableName GROUP BY ID HAVING COUNT(ID) > 1 --- <<== HERE 

---

In MySQL docs

"You can use Alias ​​if you use HAVING instead of WHERE, this is one of the distinct differences between the two sentences. It is also slower and not optimized, but if you put a complex function like this, you obviously do not expect much speed" .

Answer 3

Where the level of a single row is evaluated, while an expression is used for the group.

Answer 4

In the HAVING you can specify a condition for filtering groups , rather than filtering individual rows that occur in the WHERE phase.

Only groups for which the logical expression in the HAVING clauses are returned to TRUE by the HAVING phase. Groups for which a Boolean expression evaluates to FALSE or UNKNOWN are filtered out.

If GROUP BY not used, HAVING behaves like a WHERE . For performance comparisons, see Article.