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 >>

Subscribe to our newsletter for more free interview questions.



FOLLOW Varoon Sahgal, Author of ProgrammerInterviewon

  • Hiral

    nice expaination

  • KrishnaKumar Arunachalam

    superb

    • noname

      nice explanation…thanks bro.!!

  • puhleez

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

    • varoon10

      Thanks puhleez!

  • Venkatesh Dhanasekaran

    Throughly Understand.. Thanks Dude :-)

    • varoon10

      Thanks Venkatesh, glad it helped!

  • chicken
    • Uttam Barik

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

  • vasili74

    Thanks for the explanation..

    • varoon10

      No prob Vasili!

  • java

    mast bey

  • dick

    good example

  • SANDHYALAL KUMAR

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

  • ROcky

    nice one…Thanks!

  • sreeporna

    excellent site to clear concepts!!!

  • Vimal

    Best Site… provided good explanation

    • varoon10

      Thanks Vimal!

  • bond007

    loads of thanks…

  • parth

    Well said

  • Aditya C

    Thanks you very much very clear…

  • Vaibhav Tikoo

    Awesome Description….Thanks

  • mr

    that's good but only one point discuss……

  • Washington Norys

    Very goo!

  • Sanaya Khan

    superb