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)
Good Clarification… Thanks…
Short but Sweet.
bunch of Thanks
Thanks a lot sir, V good example
However, what about the performance impact ?
Which way of elimination works faster ?
Are there any pros/cons of one over another ?
thank u i got the concept
A procedure can call maximum how many procedures inside it?
The common nesting level is 32. Have a look at Maximum capacity specifications in SQL Server help file
Thank you good info.
Nice one Dear,
Its not tough question but no buddy think on it and just use “having” clause.
Thanks for Explanation…
If I used having as where (i.e. without group by clause), I want to know which one is faster ‘Where’ OR ‘Having’? Which one we have to prefer?
Scenario I want to use this in finding locations in certain distance using lat and longitude.
nice explanation and nice points
Good info Thank u
It was an excellent Explanation.
Sir can you please explain the concept and questions on Informatica, which are repeatedly asked in the interviews.
We can’t use HAVING without GROUP BY
Thanks Very good example keep it up man