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
Thanks, its really helpfull
Nice explanation,This Question was asked MKCL Exam…
Nice explanation, easily understandable , thanks a lot
very nice explanation, Thanks
Thanks for such an excellent explanation.
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.
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?
Thank You Sir
very useful for my intreview. Thank you very much.
hii
can i use 2 having clause in 1 sql query ???
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
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.
Hi Dave,
Thanks a lot… Nice Blog….
Regards,
ChitraPadmanaban.
Thanks a lot… Was always confused on when to use which :)
very nice answer
what the different between sql and sql server 2005
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.
Thanks all for your response which unfortunately I cant see. Anyway find the answer somewhere else.
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?
“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.