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)