SQL CASE WHEN: Labeling Rows

A raw number like quantity_on_hand is precise but not very readable in a report. CASE lets you translate values into friendly labels.

SQL checks each WHEN from top to bottom and stops at the first one that's true, so order matters. Anything left over falls through to ELSE.

select product_name,
       price,
       case
         when price >= 30 then 'premium'
         when price >= 15 then 'mid'
         else 'budget'
       end as price_band
from products

Order conditions from specific to general

A CASE expression isn't just a list of labels; it is a decision tree. Put the most specific condition first so it gets a chance to match before a broader rule catches the row.

In this lesson, quantity_on_hand = 0 belongs before quantity_on_hand < reorder_level. If the low-stock rule came first, an out-of-stock product would be labeled "low" instead of "out of stock."

Use CASE WHEN to label code values

CASE is also useful when a table stores short codes or business terms and the report needs readable labels. Garden Shop's orders carry coupon codes like SPRING10. Most orders have no coupon, and a NULL matches none of the WHEN tests, so it falls through to ELSE.

select order_id,
       coupon_code,
       case
         when coupon_code = 'SPRING10' then 'Spring promotion'
         when coupon_code = 'SUMMER15' then 'Summer promotion'
         when coupon_code = 'WELCOME5' then 'New-customer welcome'
         else 'No coupon'
       end as coupon_label
from orders