Use WHERE Rather than HAVING


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 



Oracle Database 10g SQL
Oracle Database 10g SQL (Osborne ORACLE Press Series)
ISBN: 0072229810
EAN: 2147483647
Year: 2004
Pages: 217

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net