SQL SERVER – Definition, Comparison and Difference between HAVING and WHERE Clause

« SQL SERVER – Comparison : Similarity and Difference #TempTable vs @TempVariable

SQL SERVER – 2005 Best Practices Analyzer (July BPA) »

SQL SERVER – Definition, Comparison and Difference between HAVING and WHERE Clause

July 4, 2007 by pinaldave

In recent interview sessions in hiring process I asked this question to every prospect who said they know basic SQL. Surprisingly, none answered me correct. They knew lots of things in details but not this simple one. One prospect said he does not know cause it is not on this Blog. Well, here we are with same topic online.

Answer in one line is : 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. (Reference :BOL)

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

Sometimes you can specify the same set of rows using either a WHERE clause or a HAVING clause. In such cases, one method is not more or less efficient than the other. The optimizer always automatically analyzes each statement you enter and selects an efficient means of executing it. It is best to use the syntax that most clearly describes the desired result. In general, that means eliminating undesired rows in earlier clauses.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Posted in Best Practices, SQL, SQL Authority, SQL Interview Questions and Answers, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 85 Comments

85 Responses

on July 10, 2007 at 6:08 am | Reply Graham Smith

Very helpful explanation. Thanks.
on July 10, 2007 at 9:20 pm | Reply Karthikeyan

Very Nice Explanation, Thanks a Lot.
on July 13, 2007 at 12:12 am | Reply amit namdeo

very nice explaination
on July 24, 2007 at 8:01 pm | Reply Md. Maftahur Rahman

Thanks brother, it is a nice example to understand differences between HAVING and WHERE Clause.
on July 31, 2007 at 6:01 am | Reply Ammas Sumair Bhatti

No doubt, you explain it very Well
on August 6, 2007 at 10:57 pm | Reply SQl rookie

Thanks a lot.. that was quite descriptive..
on August 23, 2007 at 12:07 am | Reply Parshu

Thanks Mr. its very usefull.

Regards
Parshu
on September 9, 2007 at 8:15 am | Reply Deepak

Thank You. Very usefull explanation.
on September 14, 2007 at 4:05 am | Reply rahul

thanks Sir its a gud defination
on September 18, 2007 at 10:26 am | Reply Harendra

Thanks alot
on September 20, 2007 at 12:09 am | Reply swapnil mohod

You have given a realy nice explanation.
on September 20, 2007 at 12:10 am | Reply swapnil mohod

Very helpful explanation. Thanks.
on November 14, 2007 at 10:16 pm | Reply sandy

Can u tell me the difference between sql server 2000 and sql server 2005 .
on November 22, 2007 at 4:45 pm | Reply Sandee

That was a very helpful explanation. This was asked to me as well. Though I answered correctly, I could not explain why I said so !!!
on November 26, 2007 at 4:33 pm | Reply Ganean.M

Thanks.

i knew the difference between these two.
can u tell me the details and difference between CUBE & ROLLUP

with regards
Ganesan.M
on January 9, 2008 at 11:56 am | Reply pradnya

thanks so much…
that was a nice and understandable example..

regards,
Pradnya
on January 14, 2008 at 7:49 am | Reply amin

thank u
on January 18, 2008 at 3:50 am | Reply Mary

Thanks for the explaination!

I really enjoy reading your articles/posts because they’re so well articulated with such simplicity that even *I* can understand it!! Thank you!
on March 17, 2008 at 12:14 am | Reply Pritam

Thanks for the EXPLAINATION…It’s really helpful for me.

Pritam
on April 9, 2008 at 6:28 pm | Reply Nitu

Hi,

I really find your BLOG very useful and interesting.

Thanks alot
on April 16, 2008 at 12:10 pm | Reply Harish

Very good explanation.

No doubt u r good mentor
on April 23, 2008 at 9:43 am | Reply Nagesh

Hi,
How to Retrieve Latest uplaoded data from database in sql server?
on May 23, 2008 at 12:51 pm | Reply Deepak Kaushik

A concise explanation. Gr8….
on June 3, 2008 at 7:57 am | Reply Naresh

i need difference between 2000 and 2005
on June 3, 2008 at 11:47 am | Reply Mayur Parmar

Your post was really helpful. I appreciate the way it was written and presented. It becomes very easy for a layman to understand it. Thanks once again.
on June 13, 2008 at 6:36 am | Reply raghu

thanks,it is informative
on June 16, 2008 at 3:53 pm | Reply Abhik

will there be any difference is performance if we use HAVING in place of WHERE in a query which doesnot has any GROUPING clauses
on June 18, 2008 at 11:15 am | Reply Abhimanyu

Normally when we are looking for something on internet the
percentage of usefull info is verry less..wht we get is normally crap.But this is one of the best explanations i have came
across…thanks a lot and keep it up.
on July 10, 2008 at 9:56 am | Reply saket

Thanks a lot
explaination is crystal clear ..
on July 16, 2008 at 5:50 pm | Reply sanjay

Its good explanation,but require diff between sql 2000 and 2005
on July 29, 2008 at 1:03 pm | Reply kumar

good one..
on July 29, 2008 at 1:04 pm | Reply kumar

good..
on August 4, 2008 at 11:57 am | Reply GK Venkaesan

Thank You Sir.
on September 22, 2008 at 11:55 am | Reply Yatindra

Superb explaination..Good work Pinal
on September 29, 2008 at 2:27 pm | Reply K.K

Hai,

The explanation is awesome..i got the exact difference now…thanks a lot..
on December 1, 2008 at 6:37 pm | Reply rasel

thanks……………
it is informative :D
on December 10, 2008 at 12:38 pm | Reply harshil

nice explanation.
example is also very helpful.
on December 16, 2008 at 8:41 pm | Reply balamuralikrishnan

can u explain difference between GROUP BY and HAVING clause ?
on February 24, 2009 at 5:39 pm | Reply SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

[...] SQL SERVER – Definition, Comparison and Difference between HAVING and WHERE Clause [...]
on February 26, 2009 at 5:33 pm | Reply SQL SERVER - 2008 - Interview Questions and Answers - Part 3 Journey to SQL Authority with Pinal Dave

[...] They specify a search condition for a group or an aggregate. But the difference is that 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. Having Clause is basically used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query. (Read More Here) [...]
on April 18, 2009 at 1:03 pm | Reply AS

GROUP BY returns single occurence of a row. However I need to limit it to a specified number of occurences.

Suppose the tabe has the following:

number letter

100 A
100 A
100 A
200 B
200 B
200 B
200 B

….and I need this to limit to say 2 occurences…the output should be…

100 A
100 A
200 B
200 B

….so on for each of the multiple rows in the table..

Can you please direct me to the right query?
- on January 20, 2010 at 10:15 pm | Reply Atif
  
  Dear AS,
  
  This can be achived by various methods, however I’ve shown you only two.
  
  For Example:
  —————
  
  You’ve a table with values like below….
  
  Create Table #Abc (SNo Int, Letter varchar(5))
  Insert Into #Abc
  Select 100, ‘A’
  Union All
  Select 100 ,’A’
  Union All
  Select 100 ,’A’
  Union All
  Select 200, ‘B’
  Union All
  Select 200, ‘B’
  Union All
  Select 200, ‘B’
  Union All
  Select 200, ‘B’
  
  and it’s output looks like this…
  
  SNo Letter
  ———– ——
  100 A
  100 A
  100 A
  200 B
  200 B
  200 B
  200 B
  
  (7 row(s) affected)
  
  –One Way With Common Table Expression
  ;With CTE
  As
  (
  Select Row_Number() Over (Partition By Letter Order By Letter) As RowId, SNo, Letter
  From #Abc )
  
  Select SNo, Letter
  From CTE
  Where RowId <= 2
  
  – Or
  – Another with Cross Apply
  
  Select a.*
  From (Select Distinct SNo,Letter From #Abc) B
  Cross Apply
  ( Select top 2 *
  From #Abc x
  Where B.SNo = X.SNo
  And B.Letter = X.Letter ) a
  
  –Drop Table #Abc
  
  I hope you'll find this helpful.
  Thanks,
  
  Atif Siddyqi
on August 4, 2009 at 12:05 am | Reply Srinivas Donavalli

Excellent. Nice explanation. Thanks a lot.
on August 22, 2009 at 9:04 pm | Reply Shital

Thanks, its really helpfull
on September 14, 2009 at 10:42 am | Reply ajit

Nice explanation,This Question was asked MKCL Exam…
on November 20, 2009 at 3:02 pm | Reply Someshwar

Nice explanation, easily understandable , thanks a lot
on November 26, 2009 at 6:24 pm | Reply sukhi

very nice explanation, Thanks
on December 1, 2009 at 9:36 pm | Reply Saurabh Kumar Sinha

Thanks for such an excellent explanation.
on December 2, 2009 at 11:45 am | Reply Hasmi

I understand that we can use a HAVING clause without a GROUP BY clause. I tried to write a few queries using AdventureWorks database, but do not understand the purpose of using a Haveing clause without the GROUP BY clause. Will appreciate if you can explain that.
on December 10, 2009 at 1:43 am | Reply Dave Dobrin

I’m wondering if there is any reason for both the WHERE and HAVING clause. Clearly WHERE filters records on fetch and/or before aggregation, and HAVING operates after aggregation. However, couldn’t the SQL compiler determine the most effecient place always. A clause based on an aggregated function (couny, sum, max, etc.), would be forced into the aggregate filter, and something without aggregated functions would be on the initial fetch. Mixing in a non-logical operator (>,100000

In this case, the clause *has* to be a having clause. That is,
select CUST,sum(REVENUE)
from order where TYPE = ‘WHOLESALE’
group by CUST
having sum(REVENUE)>100000

Incidently, thinking of this example makes me understand why non-aggregated clauses are allowed in HAVING clauses. So now, I’m thinking, using WHERE is just telling the compiler how to do something that it should be smart enough to figure out anyway.

I vote for deprecating the where clause to a hint. Any takers?
on January 9, 2010 at 5:41 pm | Reply mohanbabu

Thank You Sir
on January 26, 2010 at 7:24 pm | Reply jasmine

very useful for my intreview. Thank you very much.
on March 13, 2010 at 1:14 pm | Reply Satish

hii

can i use 2 having clause in 1 sql query ???
on March 13, 2010 at 10:36 pm | Reply Pinal Dave

Hi Satish,

You can combine multiple conditions with AND and OR operator in HAVING clause but can not write two HAVING in one query.

Regards,
Pinal dave
on March 21, 2010 at 11:54 pm | Reply Dennis Bugumba

Hi Pinal,

Very thorough and plain self explanatory description on the HAVING vs WHERE clauses you have provided. You have been very helpful with my SQL Programming & Developing course. Thanks a million bunches!

Dennis.
on May 5, 2010 at 12:58 pm | Reply chitra

Hi Dave,
Thanks a lot… Nice Blog….

Regards,
ChitraPadmanaban.
on July 8, 2010 at 1:52 pm | Reply Dev

Thanks a lot… Was always confused on when to use which :)
on August 2, 2010 at 11:29 pm | Reply suneel

very nice answer
on August 10, 2010 at 9:34 pm | Reply kido

what the different between sql and sql server 2005
on September 29, 2010 at 1:51 pm | Reply Ashish

hi all,
need help in having clause. I am having a table where there are duplicate record and some are unique record. The record filter are based on three columns and I am able to fetch the filtered records using groupby and having clause.
But problem is I want to see all the columns while my having clause can only show me those columns which I am using in filter.
Can someone suggest how can I all the columns which are filtered using having clause?.
My query is :-
select count(*) as cnt, column1,column2,column3 from tablename group by (column1,column2,column3 ) having count(*) > 1 and I have 12 other columns which I am not able to see by this.
on September 29, 2010 at 7:08 pm | Reply Ashish

Thanks all for your response which unfortunately I cant see. Anyway find the answer somewhere else.
on November 3, 2010 at 12:11 am | Reply stan

i was always under impression that you should use WHERE whenever possible, leaving HAVING to those times when where can not get the job done.

From the point of query performance, is WHERE not faster then HAVING (and vise versus)…

can anyone clear this up for me?
on December 9, 2010 at 3:29 am | Reply ASHOK kUMAR

“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.”

I found the above statement written by you WRONG. i tried the
following statement and got an error:

select * from customers having customerid=100

Conclusion: We can not use only HAVING in SELECT statement.
on January 17, 2011 at 11:50 am | Reply vilas

I would like to say only one sentence

“Very very nice article”
on January 25, 2011 at 5:21 pm | Reply Kalyani

Really Helpful article
on January 31, 2011 at 8:47 pm | Reply Mohtshm

i thought that where execute on retrieved records while having filter before fetching so i think this is a big difference in execution of a query. please guide and correct me
on February 9, 2011 at 6:35 pm | Reply Javed Khan

Hi Sir
thanks a Lot for it.
its best for me.
on March 18, 2011 at 1:25 pm | Reply kamal

parity sql server meaning
on March 23, 2011 at 3:13 pm | Reply Mohit

where is i m lacking ?

–Fetching unmatched record

select ident
from
(
select ident, full_name from position_code_abstract UNION All select position_code_abstract_ident, comments
from position_code_abstract_comment)
Group by ident
having count(*)=1
order by ident
on April 27, 2011 at 8:16 pm | Reply lilesh

thanks for ur thoughts
on May 19, 2011 at 4:25 pm | Reply Rahul.k

select Count(*) FROM Nop_Product INNER JOIN Nop_ProductVariant
ON Nop_Product.ProductID = Nop_ProductVariant.ProductID
where(Nop_Product.Color like ‘%’ + @Keywords + ‘%’ or
Nop_Product.Style like ‘%’ + @Keywords + ‘%’ or
Nop_Product.Brand like ‘%’ + @Keywords + ‘%’ or
Nop_Product.Name like ‘%’ + @Keywords + ‘%’)
group by Nop_Product.Name
having MAX(Nop_ProductVariant.StockQuantity) > 3.

I need to return the count from this query, but it is returning multiple rows and not returning actual count. Can anyone please help me as I need it urgently….
on May 20, 2011 at 8:46 am | Reply Imran Mohammed

@Rahul,

You are getting Multiple Counts because you are grouping using group by Nop_Product.Name.

That means, for every Nop_Product.Name you will see a Count. If you dont want that… you can consider your this whole query as inner query.

Here is an example,

select COUNT(*)
From (
select Count(*) Counts
FROM Nop_Product
INNER JOIN Nop_ProductVariant ON Nop_Product.ProductID = Nop_ProductVariant.ProductID
where ( Nop_Product.Color like ‘%’ + @Keywords + ‘%’
or Nop_Product.Style like ‘%’ + @Keywords + ‘%’
or Nop_Product.Brand like ‘%’ + @Keywords + ‘%’
or Nop_Product.Name like ‘%’ + @Keywords + ‘%’
)
group by Nop_Product.Name
having MAX( Nop_ProductVariant.StockQuantity) > 3.
)A

~ IM.
on June 27, 2011 at 12:22 pm | Reply iMatoria

I knew the difference when i had around 1 year of experience. Probably, you are not interviewing right prospects. :)
Anyways, you are doing great job by maintaining this blog.
on July 7, 2011 at 7:02 am | Reply SQL SERVER – Interview Questions and Answers – Frequently Asked Questions – Day 7 of 31 Journey to SQLAuthority

[...] They specify a search condition for a group or an aggregate. But the difference is that 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. Having Clause is basically used only with the GROUP BY function in a query, whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query. (Read more here) [...]
on September 2, 2011 at 5:12 pm | Reply SABARINATHAN

Nice article…i have one doubt can we use HAVING with out GROUP BY clauss.. if possible can you explain in detail, when we need to use where and having.
on September 16, 2011 at 8:01 pm | Reply Tim

Let’s see if I understand this correctly: nothing in the Group By clause can change, before or after aggregation. Therefore, any condition on these fields could be done either in a Where clause or in a Having clause.

Therefore, the only reason to use a Having clause is if I want to put in a condition based on aggregated values. But if I must have a Having clause, I may as well put all of my conditions into it, to save the bother of including both a Where and a Having clause.

Is this correct, or have I missed something?
on October 7, 2011 at 4:30 pm | Reply Gaurav Gupta

Nice article. Please put more light on performance issue in order to use Having and where clause. Also post some business scenarios in order to use Having and where.
on November 13, 2011 at 9:56 am | Reply Neeraj Kumar

Superb man
on November 18, 2011 at 4:41 pm | Reply kalaiselvi

s………this is easily understanding the people
on November 30, 2011 at 7:50 pm | Reply Igor

Is HAVING redundant?
In other words,
Any query with HAVING clause has its HAVING-less semantic equivalent.
True or false?

Same question with HAVING replaced by WHERE.
on January 9, 2012 at 7:00 pm | Reply XAVI RAJ

Nice explanation
on January 10, 2012 at 2:40 pm | Reply Tim

That’s an interesting question, Igor. There is plenty of redundancy in SQL syntax.

As regards HAVING, I suppose TOP could be used to replace some HAVING expressions, but only some.

As for WHERE, it depends on the WHERE clause. Some WHERE expressions could be more elegantly (and in some RDBMSs more efficiently) achieved using a JOIN. Most, though, cannot.
on January 14, 2012 at 7:56 am | Reply ramakrishna

Hi,
I have doubt in sqlserver.
I have one table in my db.
sname percentage
rama 86%
krishna 67%
varma 78
raghava 90%
venkat 87
radha 97
maanasa 89%

How can i get get data who is having percentage in my table?
Very important and I need immediate reply friends
Thanks
on January 14, 2012 at 8:02 am | Reply ramakrishna

I have table like this

sname gender
ramakrishna F
Sunitha M
Ramudu F
Mahalakshmi F
Revathi M
Renuka M
Raghu F
Rayudu F
Krishnan F
The client entered data male has female and female has male and some rows client entered correctly . How can i Update Male has gender male and female has gender female.

I want result like this
sname gender
ramakrishna M
Sunitha F
Ramudu M
Mahalakshmi F
Revathi F
Renuka F
Raghu M
Rayudu M
Krishnan M
on January 31, 2012 at 3:40 pm | Reply Geeta

thanks a lot