rails MySql-HAVING vs WHERE




sqlite having vs where (3)

This question already has an answer here:

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?


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.


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.


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





mysql