.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Forum >> Sql Server >> Post New QuestionBookmark and Share Subscribe to Forum

What is the difference between where and having clause in SQL Server

Posted By: priya     Posted Date: March 03, 2011    Points:2   Category :Sql Server
Hi,

What is the difference between where and having clause in SQL Server?

Thanks in advance
Priya

Responses
Author: Senthilnathan             
Posted Date: March 03, 2011     Points: 20   

Hi Priya,

We can use 'Where' to filter the data in select statement. like,

Select * from table_name where column_name='XXX'

But we can use having clause along with 'group by' and aggregate funtion only.

for example,

table
------


Name contactno
---- ---------
aa 2323
aa 42343
bb 2323
cc 2443
dd 33
ee 3434
dd 553434

if we want to find the name who are all having more than one contact no,
we can use like this,

select name from table_name group by name having count(name)>1



Author: kamlesh             
Posted Date: March 03, 2011     Points: 20   

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

Having can used only with the select statement .it is typically used with group by clause . when it is used without group by it work like where clause

http://www.w3schools.com/sql/sql_having.asp check this site

Thanks And Regards.
Kamlesh Nikam
Author: Karthikeyan Anbarasan       [Moderator]      
Posted Date: March 04, 2011     Points: 20   

Having clause is usually used with Group By clause although it can be used without it too.

Having is just an additional filter to Where clause.

Where clause applies to the individual rows whereas Having clause is used to test some condition on the group(usually aggregate methods) rather than on individual rows.

Thanks
Karthik
http://www.f5debug.net
Mark as Answer if the answer solves your issue!!!
Author: Maverick             
Posted Date: March 07, 2011     Points: 20   

Hello,

HAVING specifies a search condition for a group or an aggregate function used in SELECT statement.

HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause.

A HAVING clause is like a WHERE clause, but applies only to groups as a whole, whereas the WHERE clause applies to individual rows. A query can contain both a WHERE clause and a HAVING clause. The WHERE clause is applied first to the individual rows in the tables . Only the rows that meet the conditions in the WHERE clause are grouped. The HAVING clause is then applied to the rows in the result set. Only the groups that meet the HAVING conditions appear in the query output. You can apply a HAVING clause only to columns that also appear in the GROUP BY clause or in an aggregate function.

Example of HAVING and WHERE in one query:

SELECT titles.pub_id, AVG(titles.price)
FROM titles INNER JOIN publishers
ON titles.pub_id = publishers.pub_id
WHERE publishers.state = 'CA'
GROUP BY titles.pub_id
HAVING AVG(titles.price) > 10

Thanks,
Maverick

Thanks,
Maverick
Author: Nagaraju             
Posted Date: March 10, 2011     Points: 20   

1. Table name Qualification
EMPNO NAME QUALIFICATION
102 Raju MCA
103 Krishna SSC
104 Ramarao BSc
101 Naga BCom
101 Naga SSC


2. 'Where' Clause filter the rows based on your requirment
select * from Qualification q where q.empno=101

EMPNO NAME QUALIFICATION
101 Naga BCom
101 Naga SSC


3. Having clause is used with Group By only
select q.empno,q.name from Qualification q group by q.empno,q.name having count(q.empno)>1
EMPNO NAME
101 Naga



Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend