This question already has an answer here:<ul><li> <a href="/questions/9253244/sql-having-vs-where" dir="ltr" rel="nofollow">SQL - having VS where</a> <span class="question-originals-answer-count"> 7 answers </span> </li> </ul>
I know this is much discussed, but none of my research could convince me the difference between '
where' and '
having' clauses in MySQL. From what I understand we can achieve everything that can be done with 'where' clause using '
having' . For eg.
select * from users having username='admin'. Then why do you need '
where' clause? Does using where make any performance differences?
WHERE clause filters data from the source before aggregates, whereas
HAVING clause filters data after the
GROUP BY has been applied. Generally this means any non-aggregate filter can appear in either place, but if you have a column that is not referenced in your query, you can only filter it in a
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 only wanted to include rows where
ID > 2. If you put that in a
HAVING clause, you will get an <a href="http://www.sqlfiddle.com/#!2/f6741/17" rel="nofollow"><strong>error</strong></a>, because the
ID column is not available post aggregate as it is not in the
SELECT clause. In that case, you would be required to use a
WHERE clause instead:
select value, count(value) from Table1 where id > 2 group by value
Demo: <a href="http://www.sqlfiddle.com/#!2/f6741/16" rel="nofollow">http://www.sqlfiddle.com/#!2/f6741/16</a>Answer2:
The difference between
WHERE clause is that
HAVING supports aggregated columns while
WHERE doesn't because it is only applicable for individual rows., EG
SELECT ID FROM tableName GROUP BY ID HAVING COUNT(ID) > 1 --- <<== HERE<hr />
From the MySQL docs,
<em>"You may use Alias's if you use HAVING instead of WHERE this is one of the defined differences between the two clauses. Having is also slower and will not be optimized, but if you are placing a complex function like this in your where you obviously aren't expecting great speed."</em>Answer3:
Where evaluates on the single row level, whereas having is used for group by expressions.Answer4:
HAVING clause, you can specify a condition to filter <strong>groups</strong> as opposed to filtering <strong>individual</strong>
rows, which happens in the
WHERE phase. Only groups for which the logical expression in the
clause evaluates to TRUE are returned by the
HAVING phase . Groups for which the logical expression evaluates to
FALSE or UNKNOWN are filtered out.
GROUP BY is not used,
HAVING behaves like a
WHERE clause .
Regarding performance comparison please see this <a href="http://blog.sqlauthority.com/2007/07/04/sql-server-definition-comparison-and-difference-between-having-and-where-clause/" rel="nofollow">article</a>