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 .
Note | You can, of course, use overlapping ranges and different functions in your CASE expressions. |