Oracle Database 10g SQL (Osborne ORACLE Press Series)

Use CASE Expressions Rather than Multiple  Queries

Use CASE expressions rather than multiple queries when you need to perform many calculations on the same rows in a table. The following example uses multiple queries to count the number of products within various price ranges (bad):

-- BAD (three separate queries when one CASE statement would work) SELECT COUNT(*) FROM products WHERE price < 13; COUNT(*) ---------- 2 SELECT COUNT(*) FROM products WHERE price BETWEEN 13 AND 15; COUNT(*) ---------- 5 SELECT COUNT(*) FROM products WHERE price > 15; COUNT(*) ---------- 5

Rather than using the three queries just shown, you should write one query that uses CAS E expressions. For example:

-- GOOD (one query with a CASE expression rather than three queries) SELECT COUNT(CASE WHEN price < 13 THEN 1 ELSE null END) low, COUNT(CASE WHEN price BETWEEN 13 AND 15 THEN 1 ELSE null END) med, COUNT(CASE WHEN price > 15 THEN 1 ELSE null END) high FROM products; LOW MED HIGH ---------- ---------- ---------- 2 5 5

Notice the counts of the products with prices below $13 are labeled as low , products between $13 and $15 are labeled med , and products greater than $15 are labeled high .