The HAVING and WHERE clauses

A common programming error is to confuse the purpose of WHERE and HAVING clause filters. Don't make that mistake. Read about the differences in this book excerpt.

The following tip was excerpted from Chapter 8, 'Building Fast-Performing Data Models,' from the book "Beginning Database Design" by Gavin Powell, courtesy of WROX Publishing. Click here for the complete collection of book excerpts.

The HAVING and WHERE Clauses

A common programming error is to get the purposes of the WHERE and HAVING clause filters mixed up. The WHERE clause filters records as they are read from the database (as I/O activity takes place). The HAVING clause (part of the GROUP BY clause) filters aggregated groups, after all database I/O activity has completed. Don't use the HAVING clause when the WHERE clause could be used more efficiently, and visa versa. In the following example, the PUBLISHER_ID restriction in the HAVING clause can be moved to the WHERE clause:

SELECT ISBN, PUBLISHER_ID, AVG(LIST_PRICE)
FROM EDITION
GROUP BY ISBN
HAVING PUBLISHER_ID > 5 AND AVG(LIST_PRICE) > 10;

Move the PUBLISHER_ID restriction from the HAVING clause, to the WHERE clause, as shown by the following altered query:

SELECT ISBN, PUBLISHER_ID, AVG(LIST_PRICE)
FROM EDITION
WHERE PUBLISHER_ID > 5
GROUP BY ISBN
HAVING AVG(LIST_PRICE) > 10;

Why move the condition from the HAVING clause to the WHERE clause? As already stated, the WHERE clause executes filtering at the time that I/O activity occurs. In other words, WHERE clause filtering using the second query above, should under ideal circumstances, not even read records with PUBLISHER_ID less than or equal to 5. Leaving the PUBLISHER_ID filter in the HAVING clause will not limit I/O activity because the HAVING clause is only applied after all WHERE clause filtering, and thus I/O activity has been completed. Ensuring that any filtering that can be placed into the WHERE clause -- is in the WHERE clause, and not in the HAVING clause -- ensures best execution efficiency, in most cases.

The following tip was excerpted from Chapter 8, 'Building Fast-Performing Data Models,' from the book "Beginning Database Design" by Gavin Powell, courtesy of WROX Publishing. Click here for the complete collection of book excerpts.

This was first published in April 2006

Dig deeper on Microsoft SQL Server Performance Monitoring and Tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close