When learning to build queries in SQL you might come across different ways to achieve similar results. Choosing the correct command can often save you time and energy, as in this short tip discussing the differences between the HAVING and WHERE commands from Judith S. Bowman's book Practical SQL: The Sequel (Addison-Wesley, 2001):
In a nutshell, the WHERE command puts conditions on the table rows, determining which should be returned. HAVING puts conditions on grouped result rows. The processing order is as follows:
Select rows with WHERE.
Divide rows into sets with GROUP BY.
Calculate aggregate values for each group.
Eliminate unwanted group result rows with HAVING.
Any time you can remove rows with WHERE rather than HAVING, your query will be more efficient since there are fewer rows to group and fewer to aggregate. As you can see in the example below, it makes sense to remove 'books' before, rather than after, grouping and counting--you save a lot of work.
NO: --- SELECT type, count(*) FROM product GROUP BY type HAVING type <> 'book' YES: ---- SELECT type, count(*) FROM product WHERE type <> 'book' GROUP BY type
Use HAVING to limit group result rows, as in the following query:
SELECT type, count(*) FROM product GROUP BY type HAVING count(*) > 5
About the Author
Judith S. Bowman is author of Practical SQL: The Sequel.
For More Information
- What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
- The Best SQL Web Links
- Have an SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
- Check out our new Ask the Experts feature: Our SQL gurus are waiting to answer your toughest questions.