CASE expressions

CASE adds conditional logic in SELECT, WHERE, ORDER BY, and aggregates—SQL's answer to if/else without leaving the query.

Simple CASE

SELECT id,
       total,
       CASE
         WHEN total >= 100 THEN 'large'
         WHEN total >= 50 THEN 'medium'
         ELSE 'small'
       END AS order_size
FROM orders;

Practice: Advanced features vary by engine—SQLite 3.25+ supports window functions; test your version with a simple query.

CASE in aggregation

SELECT COUNT(CASE WHEN total >= 100 THEN 1 END) AS large_orders,
       COUNT(CASE WHEN total < 100 THEN 1 END) AS other_orders
FROM orders;

Conditional counts pivot categories in one pass—precursor to dedicated PIVOT syntax in some engines.

Searched vs simple

SELECT status,
       CASE status
         WHEN 'pending' THEN 1
         WHEN 'shipped' THEN 2
         ELSE 99
       END AS sort_key
FROM orders
ORDER BY sort_key;

Simple CASE compares to a single expression; searched CASE uses independent predicates.

Important interview questions and answers

1. Q: CASE vs IF()?
   A: CASE is standard SQL; IF is MySQL-specific.
2. Q: ELSE required?
   A: Omitting ELSE yields NULL when no branch matches—sometimes intentional.

Self-check

1. How do you bucket numeric totals into labels in SQL?
2. What value if no WHEN matches and no ELSE?

Tip: CASE in aggregates pivots categories without a separate PIVOT syntax.