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 (https://blog.sqlauthority.com)
123 Comments. Leave new
Very good explanation.
No doubt u r good mentor
Hi,
How to Retrieve Latest uplaoded data from database in sql server?
A concise explanation. Gr8….
i need difference between 2000 and 2005
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.
thanks,it is informative
will there be any difference is performance if we use HAVING in place of WHERE in a query which doesnot has any GROUPING clauses
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.
Thanks a lot
explaination is crystal clear ..
Its good explanation,but require diff between sql 2000 and 2005
good one..
good..
Thank You Sir.
Superb explaination..Good work Pinal
Hai,
The explanation is awesome..i got the exact difference now…thanks a lot..
thanks……………
it is informative :D
nice explanation.
example is also very helpful.
can u explain difference between GROUP BY and HAVING clause ?
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?
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
Excellent. Nice explanation. Thanks a lot.