Problem solve Get help with specific problems with your technologies, process and projects.

Choosing between HAVING and WHERE

Learn the differences between HAVING and WHERE commands when building SQL queries.

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:

  1. Select rows with WHERE.

  2. Divide rows into sets with GROUP BY.

  3. Calculate aggregate values for each group.

  4. 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.

   SELECT type, count(*) 
   FROM product 
   GROUP BY type 
   HAVING type <> 'book'

   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

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.