4 ms·
HAVING is not the same thing as WHERE. WHERE filters the query to produce a result set. HAVING filters the result set. select id, count(*) - 1 as nduplicat
by davidgould 8y ago
HAVING is not the same thing as WHERE. WHERE filters the query to produce a result set. HAVING filters the result set.
select id, count(*) - 1 as nduplicates
from parts where color = 'red'
group by id
having count(*) > 1;
WHERE cannot filter on the count(*) because WHERE is applied during the query before the count is known.
I agree SQL is kind of warty, and it would be great if it were easier to compose from programs, but HAVING is not redundant to WHERE.
- aidos 8y agoI believe this should have been in response to its sibling. The set theorist in me agrees - the two operations are working on 2 different sets at 2 different levels of abstraction.
- davidgould 8y agoYes, replied to the off by one post.
- irishsultan 8y agoBut that's equivalent to the following query which doesn't use having, only select select * from (select id, count(*) - 1 as nduplicates from parts where color = 'red' group by id) _inner where nduplicates > 0; Now obviously that doesn't read as easily, but WHERE can filter out the count(*) perfectly.