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 | 52 Comments

52 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.