MySql - HAVING vs WHERE [duplicate]

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?

Answer 1

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.

Answer 2

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