Difference between the having clause and where clause in sql server

1) Where Vs Having in sql server
2) Having vs Where which is better in sql server.

The main difference between Where clause and Having Clause is that Having is allays use with the group by statements  while Where is used with the Whole query.

Having Filter the data in a group not the whole result set while Where filter the data in whole result set.

Group Of 'Empid'
--Finding Duplicate Data 

Select Empid, COUNT(*) AS NoOfDuplicateRecord  From tblEmployeedetails Group by Empid Having COUNT(*) >1

Most of the time you will get the same result with Where or Having
The below given two SQL command produces the same result set.

1) Select Empid , SUM(Salary) From tblEmployeedetails Group By DeptId having Deptid='D0000234'

2) Select Empid , SUM(Salary) From tblEmployeedetails Where Deptid='D0000234' Group By DeptId

But don't confuse some time it may be but not all time as the given Example.

E001 D00120000

Select Empid , SUM(Salary) From tblEmployeedetails  Group By DeptId Having SUM(Salary) >30000

This Work can only done by having clause not through Where clause.
Where clause doesn't work with aggregate functions.

Recent Posts