You use the WHERE clause to filter rows; you use the HAVING clause to filter groups of rows. Because HAVING filters groups of rows after they have been grouped together (which takes some time to do), you should filter rows using a WHERE clause whenever possible. That way, you avoid the time taken to group the filtered rows in the first place.
The following query retrieves the product_type_id and average price for products whose product_type_id is 1 or 2. To do this, the query performs the following:
It uses the GROUP BY clause to group rows into blocks with the same product_type_id .
It uses the HAVING clause to limit the returned results to those groups that have a product _type_id in 1 or 2 (bad since a WHERE clause would work).
-- BAD (uses HAVING rather than WHERE) SELECT product_type_id, AVG(price) FROM products GROUP BY product_type_id HAVING product_type_id IN (1, 2); PRODUCT_TYPE_ID AVG(PRICE) --------------- ---------- 1 24.975 2 26.22
The next query rewrites the previous example to use WHERE rather than HAVING to first limit the rows whose product_type_id is 1 or 2:
-- GOOD (uses WHERE rather than HAVING) SELECT product_type_id, AVG(price) FROM products WHERE product_type_id IN (1, 2) GROUP BY product_type_id; PRODUCT_TYPE_ID AVG(PRICE) --------------- ---------- 1 24.975 2 26.22