rails MySql-HAVING vs WHERE
sqlite having vs where (3)
This question already has an answer here:
- SQL - having VS where 7 answers
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 )
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
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
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