7

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?

Dan D.
  • 73,243
  • 15
  • 104
  • 123
Sethunath K M
  • 4,702
  • 3
  • 28
  • 42
  • 1
    see also: [SQL - having VS where](http://stackoverflow.com/questions/9253244); [WHERE vs HAVING](http://stackoverflow.com/questions/2905292) – mellamokb Feb 26 '13 at 13:32

4 Answers4

6

The 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 WHERE clause.

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 error, 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: http://www.sqlfiddle.com/#!2/f6741/16

mellamokb
  • 56,094
  • 12
  • 110
  • 136
3

The difference between HAVING from 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

From the MySQL docs,

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

John Woo
  • 258,903
  • 69
  • 498
  • 492
  • Thanks. But I know the difference.why do you need to use where when everything can be achieved using having clause ? – Sethunath K M Feb 26 '13 at 13:33
  • in your example they do the same thing but if you are a good sql developer you should know where to use both keywords. `WHERE` is evaluated first before the `HAVING` clause. – John Woo Feb 26 '13 at 13:41
  • 1
    From the MySQL docs, *"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."* – John Woo Feb 26 '13 at 13:42
1

Where evaluates on the single row level, whereas having is used for group by expressions.

koljaTM
  • 10,064
  • 2
  • 40
  • 42
1

With the HAVING clause, you can specify a condition to filter groups as opposed to filtering individual rows, which happens in the WHERE phase.

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

When GROUP BY is not used, HAVING behaves like a WHERE clause . Regarding performance comparison please see this article

Mudassir Hasan
  • 28,083
  • 20
  • 99
  • 133