MySql - HAVING vs WHERE

Question

What is the difference between these 2 queries?

SELECT f.name, 
       u.name, 
       u.id 
FROM   families f 
       JOIN units u 
         ON f.unit_id = u.id 
HAVING u.id IN( 43, 413, 22 )

And:

SELECT f.name, 
       u.name, 
       u.id 
FROM   families f 
       JOIN units u 
         ON f.unit_id = u.id 
WHERE  u.id IN( 43, 413, 22 )

The result of these 2 queries is exactly the same. So where is the difference?

An even earlier, better duplicate can be found at http://stackoverflow.com/questions/2905292/where-vs-having — thomasrutter, Jan 22 '14 at 11:51

score 77 · Answer 1 · answered Apr 22 '13 at 20:20

77

WHERE is used to select data in the original tables being processed.

HAVING is used to filter data in the result set that was produced by the query. This means it can reference aggregate values and aliases in the SELECT clause.

For instance, can write:

SELECT t1.val - t2.val diff
FROM t1 JOIN t2 ON (some expression)
HAVING diff > 10

This wouldn't work using WHERE because diff is an alias, not one of the original table columns. You could write instead:

SELECT t1.val - t2.val diff
FROM t1 JOIN t2 ON (some expression)
WHERE t1.val - t2.val > 10

but then it may have to do all the subtractions twice: once for selecting, and again to produce the result set.

answered Apr 22 '13 at 20:20

Barmar

741,623
53
500
612

However, `having` can also be used in normal non-aggregate predicates that work with `where`. E.g. `where 1=1` vs `having 1=1`. Why is this allowed? – Pacerier May 10 '15 at 08:51
2

`HAVING` simply evaluates the expression for each row in the result set. If it's true, it keeps the row in the results, otherwise it removes it. Any expression is allowed, the only difference is that it's in a scope where the aliases are available as well. – Barmar May 10 '15 at 18:09
I mean, doesn't it not make sense to put non aggregate comparisons in `having`? Shouldn't they have been put in `where`? – Pacerier May 24 '15 at 20:36
2

`WHERE` can only rererence columns in the original table, it can't reference aliases in the `SELECT`. `WHERE` filters before producing the result set, `HAVING` filters after. While it's usually used on the result of aggregation, it can be used on anything in the result set. It's really just a shorthand for putting the original query in a subquery, and then using a `WHERE` clause in the outer query. – Barmar May 24 '15 at 20:46
Ic, so you are saying that shifting non-aggregate filters from `having` to `where` might not exactly return us the same result set? – Pacerier May 26 '15 at 03:27
@Pacerier If the filter refers to an alias in the `SELECT` clause, shifting it from `HAVING` to `WHERE` will cause an error. In my example, if you tried `WHERE diff > 10` it will say that the column `diff` does not exist. – Barmar May 26 '15 at 04:43
one thing is, if you can discard a result in a `WHERE` filter, why wait until we are at the `HAVING` stage? a `WHERE` filter happens during the first scan for results while a `HAVING` is more similar to wrapping the entire select statement in another query where you do have access to aliases in a `WHERE` clause. – santiago arizti Aug 26 '16 at 16:43
@santiagoarizti Sometimes the calculation is complicated, and doing it twice will be expensive. Consider a query that calculates distances using the haversine formula and wants to show the locations that are within a small distance. Doing it both in the `SELECT` and `WHERE` will double the size of the query and may perform this complex math twice. – Barmar Aug 26 '16 at 16:46
@Barmar oh yes, I agree with you, I was just explaining why to use `where` whenever possible. One might think that instead of learning the two commands strengths and weeknesses, one had better just learn the one that can be used in most scenarios (`having`) without knowing that it comes at its own cost – santiago arizti Aug 26 '16 at 16:49

score 49 · Accepted Answer · edited Jun 20 '20 at 09:12

49

Difference between the having and where clause in sql is that the where clause can not be used with aggregates, but the having clause can. One way to think of it is that the having clause is an additional filter to the where clause.

Which is better : click

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 22 '13 at 20:24

Ramesh Rajendran

37,412
45
153
234

3

Great article and explanation. Thanks. – Noam B. Apr 22 '13 at 20:37
That article is incredibly helpful, +1 – ocean800 Jul 24 '15 at 21:27
2

Is this link working? I am trying to access but it says invalid address. Please check if its just me getting error. – Ankush G Jul 14 '17 at 12:19
Yes it's working. may be you have some proxy problem – Ramesh Rajendran Jul 14 '17 at 12:44
link does not work for me. Seeing ERR_CONNECTION_TIMED_OUT – Santosh Pillai Apr 05 '18 at 13:44
what about performance ? - in a practical experience of mine, WHERE drastically reduce the number of total entries to ORDER BY where HAVING is slower - in my experience it's better to have even duplicated complex conditions in the WHERE clause vs re-use the SELECT field in a HAVING clause. – cancerbero Apr 19 '22 at 22:29

score 12 · Answer 3 · answered Apr 22 '13 at 20:17

12

In these queries, nothing. But if you were to use a GROUP BY you would see a difference. If you were to use a GROUP BY the HAVING would be applied to the group whereas the WHERE would be applied to the SELECT before grouping the data.

answered Apr 22 '13 at 20:17

Mike Perrenoud

66,820
29
157
232

3

Can you give an example in which I will be able to see the difference in this query? – Noam B. Apr 22 '13 at 20:22
1

I can. `SELECT shapeType, count(shapeType) countShapeType from shapes group by shapeType HAVING countShapeType > 10` In this query, you select all your shapes grouped by shape types, and only displaying those types of which you have more than 10. You would not be able to do this in a where statement because there you have no access to the count aggregate. – santiago arizti Aug 26 '16 at 16:46

MySql - HAVING vs WHERE

3 Answers3

Linked