Showing posts with label where vs having. Show all posts
Showing posts with label where vs having. Show all posts

Wednesday 7 December 2016

Difference Between Where and Having Clause



The WHERE clause does not work with aggregates like SUM,Count etc.

The SQL above will not work, because the where clause doesn’t work with aggregates – like sum, avg, max, etc.. Instead, what we will need to use is the having clause. The having clause was added to sql just so we could compare aggregates to other values – just how the ‘where’ clause can be used with non-aggregates. Now, the correct sql will look like this:

Many time i have seen code where Having and where clause are misused see Examples Below -

BAD SQL:
select employee, sum(bonus) from emp_bonus
group by employee where sum(bonus) > 1000;

GOOD SQL:
select employee, sum(bonus) from emp_bonus
group by employee having sum(bonus) > 1000;


Difference between having and where clause

So we can see that the 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.

Sql Standard Theory says WHERE Clause restricts the result set before returning rows and HAVING restricts the result set after retrieving all records.That's why WHERE is faster as compared to Having and that's why having is not used more commonly or always as that of Having so it should used in circumstances where it is necessary to use having and nothing else is possible like as in case we are using aggregate functions