Having V.S. Where

[vicki]

Hi,



is there any different from having and where? Which one is more efficiency? - thx

SELECT testedYear, Count(testedDate) AS countOfTestedDate
FROM results
GROUP BY testedYear
HAVING testedYear=2014




SELECT testedYear, Count(testedDate) AS countOfTestedDate
FROM results
where testedYear=2014
GROUP BY testedYear

[orange]

Here is an description with samples.

[pbaldy]

I actually don't like that description, though it's technically true. The WHERE clause is applied before the aggregation, the HAVING clause is applied after (which is why WHERE can't use the aggregate functions; they haven't been done yet). Using your SQL, the WHERE clause should be more efficient. The HAVING clause would pull all records, perform the count, then only return 2014. The WHERE clause would only pull 2014 records, then perform the count and return the records. On smaller databases you probably wouldn't see a difference, but in larger ones you could.

[orange]

I'm not saying it's the best example -- it was quick; relatively simple; and most comments were very positive.
I could not find an example from Simply SQL (can't find a link at the moment).