In recent interview sessions during the hiring process, I asked a simple question to every prospect who claimed to know basic SQL. Surprisingly, none of them answered correctly. They knew many details about SQL, but not this simple concept. One prospect even said he didn’t know because it wasn’t mentioned on a particular blog. Well, here we are, discussing the topic online.

The answer, in one line, is: HAVING specifies a search condition for a group or an aggregate function used in a SELECT statement. The HAVING clause can only be used with the SELECT statement and is typically used in conjunction with a GROUP BY clause. When GROUP BY is not used, the HAVING clause behaves like a WHERE clause.

A HAVING clause is similar to a WHERE clause, but it 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, and 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, and only the groups that meet the HAVING conditions appear in the query output. It is important to note that a HAVING clause can only be applied to columns that also appear in the GROUP BY clause or in an aggregate function.

Let’s take a look at an example of using both the HAVING and WHERE clauses in a single 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, which generally means eliminating undesired rows in earlier clauses.

Understanding the HAVING clause is essential for writing complex queries that involve grouping and aggregating data. It allows you to filter the results based on conditions applied to groups rather than individual rows. By mastering this concept, you can enhance your SQL skills and become more proficient in working with SQL Server.