CASE WHEN: Writing if-else Inside a SQL Query

Exporting to Excel to categorise data costs two hours a week. Doing it in SQL costs four lines.

The problem

Bucket exam scores into Excellent / Good / Weak. No export, no Python:

SELECT
  id,
  score,
  CASE
    WHEN score >= 85 THEN 'Excellent'
    WHEN score >= 70 THEN 'Good'
    ELSE 'Weak'
  END AS grade
FROM exams;

Common mistakes

1. Conditions in the wrong order

CASE stops at the first matching condition. Reverse the order and everything breaks:

-- WRONG: every score of 70+ becomes 'Good'
CASE
  WHEN score >= 70 THEN 'Good'
  WHEN score >= 85 THEN 'Excellent'
END

A score of 92 matches the first line and never reaches the second. The rule: order from narrowest to broadest.

2. Omitting ELSE

Without ELSE, unmatched rows return NULL — and that NULL goes on to break COUNT and GROUP BY downstream. Always write ELSE.

3. Comparing to NULL

-- Never true
CASE WHEN score = NULL THEN 'Not taken' END

-- Correct
CASE WHEN score IS NULL THEN 'Not taken' END

Three other places CASE belongs

In ORDER BY — custom sort order

ORDER BY CASE status
           WHEN 'urgent' THEN 1
           WHEN 'in_progress' THEN 2
           ELSE 3
         END;

In SUM — conditional counting

SELECT
  COUNT(*)                                        AS total,
  SUM(CASE WHEN score >= 85 THEN 1 ELSE 0 END)    AS excellent
FROM exams;

This is the most important technique in the article — every pivot report is built on it.

In GROUP BY — custom buckets

SELECT
  CASE WHEN age < 25 THEN '18-24'
       WHEN age < 35 THEN '25-34'
       ELSE '35+' END AS age_band,
  COUNT(*)
FROM users
GROUP BY 1;

Practice

• LeetCode 610 — Triangle Judgement
• LeetCode 1873 — Calculate Special Bonus
• LeetCode 627 — Swap Salary
• LeetCode 1179 — Reformat Department Table
• HackerRank — Type of Triangle