What is Difference Between HAVING and WHERE – Interview Question of the Week #068

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:

What is Difference Between HAVING and WHERE - Interview Question of the Week #068 difference-having-where-800x369

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)

,
Previous Post
SELECT One by Two – Why Does SELECT 1/2 Returns 0 – Interview Question of the Week #067
Next Post
SELECT One by Two – Interview Question Extended – Part II

Related Posts

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

    Reply
  • 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.

    Reply
  • Tribhuwan Mishra
    May 10, 2016 5:56 pm

    actually this is not enough Ans for group-by and having clause Pinal ji explain please deeply…..

    Reply
  • you cannot use having without group by

    Reply

Leave a Reply

Menu