Difference between the having clause and where clause in sql server
Similar:
1) Where Vs Having in sql server
2) Having vs Where which is better 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.
Ex:
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.
| Empid | DepId | Salary |
|---|---|---|
| E001 | D001 | 20000 |
| E002 | D001 | 22000 |
| E003 | D002 | 18000 |
| E004 | D002 | 19000 |
| E005 | D002 | 20000 |
| E006 | D003 | 21000 |
| E007 | D003 | 30000 |
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.
