Difference Between Having and Where Clause in Sql Server

Both Having Clause and Where clause is used to filter the data coming from the Select statement, but still there are some differences between them. These difference are given below:-
To show the difference between the Where Clause and the Having clause we will going to use the table EmployeeDeptInfo whose create query statement  is given below :-
Create table EmployeeDeptInfo ( Employeeid int, Departmentid int)
and it contains the following data 
Employeeid              Departmentid
1                                  1
2                                  2
3                                  2
4                                  3
3                                  2
2                                  2
5                                  4
2                                  2
1) Where clause can be used with Select, Update and Delete Statement Clause but having clause can be used only with Select statement.
For example, the sql query
         Update EmployeeDeptInfo Set departmentid =7 Where employeeid=4 
will work fine but the query
         Update EmployeeDeptInfo Set departmentid =7 Having employeeid=4
will not work
2) We can’t use aggregate functions in the where clause unless it is in a subquery contained in a HAVING clause whereas  we can use aggregate function in Having clause. We can use column name in Having clause but the column must be contained in the group by clause.
For example,  the sql query
select * from EmployeeDeptInfo where count(employeeid)>1 
will not  work but the query
Select Employeeid, Departmentid from EmployeeDeptInfo Group By Employeeid, DepartmentId having (count(employeeid) >1)
will work fine
3) Where Clause is used on the individual records whereas Having Clause in conjunction with Group By Clause work on the record sets ( group of records ).
For Example, in the below sql Query
 select employeeid, departmentid from EmployeeDeptInfo where employeeid=5
the where clause will  search the table EmployeeDeptInfo for the record whose employeeid is 5 and then show the output.
but in the below query,
Select Employeeid, Departmentid from EmployeeDeptInfo Group By Employeeid, DepartmentId having  employeeid=5
the result are  first grouped by the Group By Clause and then they become again filtered by the condition defined in the having clause. Sometime , like above both queries, we get the same result with the help of Where clause and having clause but which way is best is determined automatically by the optimizer and it select the best way of executing it.
   

DMCA.com

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in Imp SQL Difference, Interview Questions, SQL Group By Having, SQL Server and tagged . Bookmark the permalink.

39 Responses to Difference Between Having and Where Clause in Sql Server

  1. Ganesh joshi says:

    tks a lot super differences with a great example.

  2. admin says:

    Thanks Ganesh….:-)

  3. Anonymous says:

    Really nice explanation for interview basis

    gonna check more posts 🙂

  4. Anonymous says:

    nice article for interview basics…..

  5. Anonymous says:

    Very good Explanation

  6. lakshmi says:

    nice example but in what are all the conditions we can use where and having clauses? can you give answer for this quetion

  7. Anonymous says:

    excellent

  8. Anonymous says:

    excellent sir

  9. KK VINOD says:

    Clearly explained thanks Vivek.

  10. Sonali says:

    ITS REALLY HELPFULL FOR INTERVIEW..

  11. Anonymous says:

    Thanks a lot…great explanation

  12. ikram says:

    Thanks vivek nice explanation

  13. kailash says:

    Best yaar

  14. admin says:

    Thanks

  15. vaibhav says:

    Thanks a lot..Really super explanation for interview basis.

    gonna check more posts..

  16. Pingback: פורוםליווי

  17. Pingback: ביזנס ליידיס

  18. Pingback: agencia de viajes en managua

  19. Pingback: บัตร เครดิต อนุมัติ ทันที

  20. Pingback: MY CLICKFUNNEL BUSINESS

  21. Pingback: Best Tallahassee real estate agent

  22. Pingback: Istanaimpian2

  23. Pingback: blog

  24. Pingback: https://foto-rob.pl/

  25. Pingback: myip

  26. Pingback: cell phone repair in north miami beach florida

  27. Pingback: where to buy wall art

  28. Pingback: mejaqq

  29. Pingback: contact us

  30. Pingback: xem bongdatructiep

  31. Pingback: w88

  32. Pingback: movies

Leave a Reply