I do a performance tuning consulting to earn my livelihood. Once in a while, I end up customer who wants to interview me to check if I know SQL. I absolutely have no problem with the interview. This is a good thing and it gives me confidence that they are serious with their project. With said that once in a while the senior guys on the other side are so busy they end up having my interview with junior (novice) experts. I often struggle with this interview question and answer with beginners because my answers are based on experience and they often do not match what they have learned in school/collage. Here is a question which I was asked last week:
Interview Question: What is the difference between Having and Where clause?
Answer:Â HAVING specifies a search condition for a group or an aggregate function used in a 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.
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
There are situations when we can the same result when we use WHERE clause or HAVING clause. In such cases, one method is not more or less efficient than the other. The optimizer is smart enough to select the best execution plan for queries.
Reference: Pinal Dave (https://blog.sqlauthority.com)
4 Comments. Leave new
I believe that If you are not doing any arithmetic on values (sum, avg, etc) in an aggregation over a domain of records, “where” is faster because you do not have to render the results of the entire select statement and then filter the results using “having”.
This is not quite right. The model SQL is that a having clause without a group by treats the result set as if it were a single group and works on it in the usual fashion. This is not by what happens in a where clause.
As an aside for all SQL Server users, you used to be able to use the += operator in the having clause predicate. Nobody was quite sure what it meant, but it was legal syntax back then. Please do not ever do that again.
actually this is not enough Ans for group-by and having clause Pinal ji explain please deeply…..
you cannot use having without group by