SQL SERVER – Definition, Comparison and Difference between HAVING and WHERE Clause

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. The question was the difference between HAVING and WHERE clause.

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.

A Simple Way to Remember the HAVING and WHERE Clause

I remember it by the logical order of a query: first filter the rows, then group them, then filter the groups. WHERE belongs to the first step, so it cannot see totals like SUM or COUNT. HAVING belongs to the last step, so it can.

A common mistake is putting a plain row filter in HAVING, like a date range or a region. The answer is the same, but the query reads as if the filter needs the groups. Keep row filters in WHERE and aggregate conditions in HAVING, and the next reader knows exactly what you meant.

To test yourself, write a query that lists customers with more than five orders this year. It needs both clauses.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.