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.
Share this:
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
tks a lot super differences with a great example.
Indeed, I agree with you Ganesh. I read couple of articles and it seems of good quality. Thanks Vivek for sharing our knowledge with community. By the way I also blog and have shared my 2 cents on same topic as How WHERE and HAVING clause work with GROUP BY clause, let me know how do you find it. Thanks
Thanks Ganesh….:-)
Really nice explanation for interview basis
gonna check more posts 🙂
Thanks for your feedback:-)
nice article for interview basics…..
Thanks for your feedback 🙂
Very good Explanation
Thanks
nice example but in what are all the conditions we can use where and having clauses? can you give answer for this quetion
excellent
Thanks
excellent sir
Thanks
Clearly explained thanks Vivek.
Thanks Vinod
ITS REALLY HELPFULL FOR INTERVIEW..
Thanks a lot…great explanation
Thanks vivek nice explanation
Best yaar
Thanks
Thanks a lot..Really super explanation for interview basis.
gonna check more posts..
… [Trackback]
[…] Read More on that Topic: techmixing.com/2010/01/difference-between-having-and-where.html […]
… [Trackback]
[…] Information to that Topic: techmixing.com/2010/01/difference-between-having-and-where.html […]
… [Trackback]
[…] Information on that Topic: techmixing.com/2010/01/difference-between-having-and-where.html […]
… [Trackback]
[…] Find More on to that Topic: techmixing.com/2010/01/difference-between-having-and-where.html […]
… [Trackback]
[…] Read More Information here on that Topic: techmixing.com/2010/01/difference-between-having-and-where.html […]
… [Trackback]
[…] Here you will find 37019 more Info on that Topic: techmixing.com/2010/01/difference-between-having-and-where.html […]
… [Trackback]
[…] There you can find 77631 more Information to that Topic: techmixing.com/2010/01/difference-between-having-and-where.html […]
… [Trackback]
[…] Read More Information here to that Topic: techmixing.com/2010/01/difference-between-having-and-where.html […]
… [Trackback]
[…] Find More on to that Topic: techmixing.com/2010/01/difference-between-having-and-where.html […]
… [Trackback]
[…] Here you can find 27799 additional Information on that Topic: techmixing.com/2010/01/difference-between-having-and-where.html […]
… [Trackback]
[…] Information to that Topic: techmixing.com/2010/01/difference-between-having-and-where.html […]
… [Trackback]
[…] There you can find 68530 additional Information to that Topic: techmixing.com/2010/01/difference-between-having-and-where.html […]
… [Trackback]
[…] Info on that Topic: techmixing.com/2010/01/difference-between-having-and-where.html […]
… [Trackback]
[…] There you will find 90407 more Information on that Topic: techmixing.com/2010/01/difference-between-having-and-where.html […]
… [Trackback]
[…] Info to that Topic: techmixing.com/2010/01/difference-between-having-and-where.html […]
… [Trackback]
[…] Here you will find 63826 more Information to that Topic: techmixing.com/2010/01/difference-between-having-and-where.html […]
movies
movies