mysql - rails - sqlite having vs where
MySql-HAVING vs WHERE (2)
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.
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?
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.