What is the difference between WHERE clause and HAVING clause?

Where clause and Having clause will have the same functionality, but the following are the some differences.

• By using Where clause, we can perform select, insert, update statements.
• By using Having clause, we can perform only select statement.
• We can not perform aggregate functions on where clause, except in sub queries.
• We can perform aggregate functions on Having clause.
• Another difference is that where clause occurs before GROUP BY and having clause occurs after GROUP BY.

Note: We can perform a select statement in where clause and having clause.


The following is an example.

Le us consider a table as follows.


Create table ITServices(

    Items nvarchar(100),
    AmountSale int
)
Go

Insert into ITServices values ('Nokia', 1500)
Insert into ITServices values ('Lapy', 1800)
Insert into ITServices values ('Nokia', 2000)
Insert into ITServices values ('sound box', 1400)
Insert into ITServices  values ('Lapy', 1600)
Go

select * from ITServices

Compile the query, then the result will be as follows.

Now, if you want to find a total amount sale of items then we need to use GROUP BY clause as follows.

select items, sum(amountsale) as TotalAmount from ITServices group by Items

Now the result will be as follows.

Here, if you want to find TotalAmount > 1500 then we need to use HAVING clause as follows. 

select items, sum(amountsale) as TotalAmount from ITServices group by Items

having sum(AmountSale)>1500

Now the result will be as follows.

In the same query instead of HAVING if you use WHERE clause you will find an error, so as we discussed in above where clause does not work with aggregate functions.

                      If you know any differences, please post in the comment box.