Unit 09.02: CASE buckets and the missing ELSE

Unit ID: SQL-M09-U03 - Estimated active time: 13-16 minutes Objective: build category expressions that account for every row, including the ones you did not anticipate.

A CASE without ELSE returns NULL

SELECT COUNT(*)
FROM (SELECT CASE WHEN country = 'IN' THEN 'India' END AS bucket FROM customers)
WHERE bucket IS NULL;
-- 1999

1,999 customers fell through and became NULL: 1,699 with a non-India country and 300 with no country at all. If that expression fed a report, those 1,999 would appear as a blank category - or, after a WHERE bucket = … filter, vanish entirely.

Always close the expression

SELECT CASE
         WHEN country IS NULL THEN 'Unknown'
         WHEN country = 'IN'  THEN 'India'
         ELSE 'International'
       END        AS segment,
       COUNT(*)   AS customers
FROM customers
GROUP BY segment
ORDER BY customers DESC;
-- India         | 2813
-- International | 1699
-- Unknown       |  300

2,813 + 1,699 + 300 = 4,812. The buckets sum to the total, which is the property to check every time.

Order matters

CASE evaluates top to bottom and stops at the first match. Put the NULL test first: if country = 'IN' came first it would be UNKNOWN for NULL rows and fall through - correct here by luck, but fragile. Testing for the special case explicitly is what makes the logic robust rather than accidental.

The reconciliation check

SELECT SUM(customers) AS bucketed, (SELECT COUNT(*) FROM customers) AS total
FROM (
  SELECT CASE
           WHEN country IS NULL THEN 'Unknown'
           WHEN country = 'IN'  THEN 'India'
           ELSE 'International'
         END AS segment,
         COUNT(*) AS customers
  FROM customers GROUP BY segment
) t;
-- 4812 | 4812

If those two numbers differ, a row escaped your buckets. This is the same "do the parts sum to the whole" discipline as Module 3's three-way split and Module 4's grain check.

Practice

Build a three-band rating segmentation over feedback that accounts for all 500 rows, and prove it does.

Check your answer

SELECT CASE
         WHEN rating IS NULL THEN 'No rating'
         WHEN rating < 3     THEN 'Low'
         ELSE 'High'
       END      AS band,
       COUNT(*) AS responses
FROM feedback
GROUP BY band
ORDER BY responses DESC;
-- High      | 260
-- Low       | 180
-- No rating |  60

260 + 180 + 60 = 500. Without the NULL branch, the 60 unrated rows would have become NULL and been read as a data error rather than a real category.

Takeaway

Every CASE needs an ELSE, and every bucketing needs a check that the parts sum to the whole.

---