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

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

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

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!!!

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

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