In SQL, what’s the difference between the having clause and the where clause?

 

The difference between the having and where clause is best illustrated by an example. Suppose we have a table called emp_bonus as shown below. Note that the table has multiple entries for employees A and B.

emp_bonus
Employee Bonus
A 1000
B 2000
A 500
C 700
B 1250

If we want to calculate the total bonus that each employee received, then we would write a SQL statement like this:

select employee, sum(bonus) from emp_bonus group by employee;

The Group By Clause

In the SQL statement above, you can see that we use the "group by" clause with the employee column. What the group by clause does is allow us to find the sum of the bonuses for each employee. Using the ‘group by’ in combination with the ‘sum(bonus)’ statement will give us the sum of all the bonuses for employees A, B, and C.

Subscribe to our newsletter for more free interview questions.

Running the SQL above would return this:

Employee Sum(Bonus)
A 1500
B 3250
C 700

Now, suppose we wanted to find the employees who received more than $1,000 in bonuses for the year of 2007. You might think that we could write a query like this:

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

The WHERE clause does not work with aggregates like SUM

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:

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.

Hiring? Job Hunting? Post a JOB or your RESUME on our JOB BOARD >>

FOLLOW Varoon Sahgal, Author of ProgrammerInterviewon

50 Responses to “Having vs. Where clause”

  1. Just keep in mind that the only people who never make mistakes or fail are either people who never do anything with their life or people who are already dead. If you want to really live life, do things that you think will make you happy and give you excitement even if it’s scary.
    [url=http://www.bassophac.net/03/]nike roshe run nm[/url]

  2. Jordan 12 says:

    You’ve probably experienced this in the past. The new year starts, you’re determined and get right to working on your goals. Things are going well for the first couple of weeks or even the first month but then all of the sudden, you find yourself a bit less motivated. You might skip taking action for a day or two due to your very reasonable excuses.
    [url=http://www.related-work.org/04/]Jordan 12[/url]

  3. Before invading Iraq, Bush, Chency and Rumsfeld had convinced not only, Americans but the invading armies as well that they would be greeted not as invaders, but as liberators. The solders advanced on Basra, Najf and Nasiriya hade expected cheering crowds and garlands. They met with rockets and bullets instead. The US blamed these on fedayeen, a word that no one had heard in the context of Iraq till then, on Baath Party fanatics and Saddam’s loyalists.
    [url=http://www.agoraevangelism.net/71/]Nike Roshe Run[/url]

  4. Jai Ganesh Jayaraman says:

    Nice explanation.
    Thanks

  5. Amod says:

    Awesome explanation.

  6. Dolly says:

    Very well explained !!
    Thanks :)

  7. gnanasundaram mano says:

    Okay….

  8. qin says:

    Is there a way where we can use the functions in having but have a better efficiency?

  9. abhay says:

    I rate this website ***** 5/5 stars. This website is a big achievement for its developers and content providers. (Y)

  10. BHS says:

    Great point !

  11. shweta says:

    does using ‘ having ‘ increases the performance of the query better than the ‘where’ clause in sql queries and how? Please explain

  12. cromateu says:

    this is nice..thanks a ton

  13. Ed says:

    Very good explanation! A little addendum – a follow-up question might be: Is there a disadvantage to using HAVING, especially if there’s an equivalent query with WHERE?

    The answer is: There could be performance issues. Queries with the HAVING statement need to filter the result set with O(n) (n being the number of records in the result set) for the HAVING expressions, while WHERE clauses can use indexes and other optimizations, making it possible to have O(log n) or even O(1) execution time complexity.

    The reason for this is exactly the reason why HAVING exists in the first place: The DB generates the result set with the WHERE expression *before* the aggregate function is executed (so it can’t be evaluated *after* it). Since there can’t be indexes on result sets (they are dynamic, after all), a HAVING filter has to check each result row.

    Hope it helps!

  14. DPJ says:

    No, as the author said, you cannot use aggregate functions in WHERE clause. So the first query will not execute, guess you would get a syntax error there.

  15. Kailash Aade says:

    Thanks dude.. really helped a lot

  16. anna says:

    thanks a lot ! good example !

  17. Sushil Kumar says:

    thanks sir!

  18. brendan says:

    It might be nice to show the different output of the queries. I assume it would look like this:

    WHERE
    EmployeeSum(Bonus)A1000B3250

    HAVING
    EmployeeSum(Bonus)A1500B3250

    Is this a correct assumption?

  19. Qasim says:

    Thank you very very much,
    After studying from 5-6 sites I was still in confusion
    but from here i clear my concept in only 5 minutes.

    Thanks , excellent explanation.

  20. ashok says:

    Good one bro

  21. Swapna says:

    nicely explained.. thanku :)

  22. Avinash says:

    Hey thanks man…. it doesnt get any simpler than this…. great explanation

  23. robin says:

    Thanks all my doubt are cleared now

  24. L says:

    Thanks.. that explains it perfectly..

  25. JK says:

    Thanks boss… good explanation with the example…

  26. Sanaya Khan says:

    superb

  27. Washington Norys says:

    Very goo!

  28. mr says:

    that's good but only one point discuss……

  29. Vaibhav Tikoo says:

    Awesome Description….Thanks

  30. varoon10 says:

    Thanks puhleez!

  31. varoon10 says:

    No prob Vasili!

  32. varoon10 says:

    Thanks Vimal!

  33. varoon10 says:

    Thanks Venkatesh, glad it helped!

  34. Aditya C says:

    Thanks you very much very clear…

  35. parth says:

    Well said

  36. bond007 says:

    loads of thanks…

  37. Vimal says:

    Best Site… provided good explanation

  38. sreeporna says:

    excellent site to clear concepts!!!

  39. noname says:

    nice explanation…thanks bro.!!

  40. ROcky says:

    nice one…Thanks!

  41. SANDHYALAL KUMAR says:

    Very good explanation of each covered topic , helpful , interesting and clear……

  42. dick says:

    good example

  43. java says:

    mast bey

  44. vasili74 says:

    Thanks for the explanation..

  45. Uttam Barik says:

    chicken. it is simple you can not use group function in where clause. it is not misleading

  46. Venkatesh Dhanasekaran says:

    Throughly Understand.. Thanks Dude :-)

  47. puhleez says:

    This is one of those basic concepts that I was having trouble wrapping my mind around. Excellent, simple example. Thanks!!!

  48. KrishnaKumar Arunachalam says:

    superb

  49. Hiral says:

    nice expaination

Leave a Reply

*