Wednesday, April 29, 2015

Difference between Having and Where clause in Sql Server

Introduction: In this article today I am going to explain the difference between Having and Where clause in Sql Server

Description:
In this example I am going to explain what are the difference between Having and Where clause in Sql Server. This question is commonly asked in interview.

I have an table Student_Detail :

Id
int
SudentName
varchar(50)
Fee
int
StudentClass
varchar(50)
StudentRollNo
int

  


1.> We can use the Where Clause with Select, update and Delete statements but having clause can be used only with Select statement.

2.> Having Clause used to filter groups but Where clause is used to filter the rows i.e. applies on each and every row.

3.> We can’t use the Where clause with the aggregate function but can use the Having clause with aggregates function.
Example:
select SudentName , sum(Fee) as Fee from dbo.Student_Detail group by SudentName where  sum(Fee) >1000

If we run the query it will not work.

select SudentName , sum(Fee) as Fee from dbo.Student_Detail group by SudentName having sum(Fee) >2500

4.> Difference on the relationship of Group by clause is that we use the Where before the Group By clause and Having clause is used after the Group By clause.
Example:

select SudentName , sum(Fee) as Fee from dbo.Student_Detail where SudentName='john' group by SudentName having sum(Fee) >1000


2 comments: