How to use CASE expressions in SQL

Short answer

CASE provides if-then-else logic within a query. It can be used as a simple switch or as a searched expression with boolean conditions.

Steps

1. Simple CASE: SELECT CASE grade WHEN 'A' THEN 'Excellent' WHEN 'B' THEN 'Good' ELSE 'Average' END FROM scores;
2. Searched CASE: SELECT CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' ELSE 'C' END FROM scores;
3. Use in aggregates: SELECT SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) FROM orders;
4. Use in ORDER BY: ORDER BY CASE WHEN priority = 'high' THEN 1 ELSE 2 END
5. Wrap in a column alias for readability.

Tips

• Always include an ELSE clause to avoid unexpected NULL results.
• CASE short-circuits: conditions are evaluated in order.

Common issues

• Mismatched data types across THEN branches cause type conversion errors.
• Using CASE inside WHERE is redundant; filter with WHERE directly when possible.r