Manage Learn to apply best practices and optimize your operations.

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 last published in April 2006

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

  • How do I size a UPS unit?

    Your data center UPS sizing needs are dependent on a variety of factors. Develop configurations and determine the estimated UPS ...

  • How to enhance FTP server security

    If you still use FTP servers in your organization, use IP address whitelists, login restrictions and data encryption -- and just ...

  • 3 ways to approach cloud bursting

    With different cloud bursting techniques and tools from Amazon, Zerto, VMware and Oracle, admins can bolster cloud connections ...

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close