Conditional logic with CASE

The CASE expression

CASE is SQL's "if/else": it evaluates conditions in order and returns the value of the first one that holds. It is an expression, so it can go anywhere a value fits: in the SELECT, in the ORDER BY, etc.

SELECT
  name,
  price,
  CASE
    WHEN price < 50  THEN 'cheap'
    WHEN price < 200 THEN 'medium'
    ELSE 'expensive'
  END AS tier
FROM products;

• Each WHEN ... THEN ... is a condition and its result.
• They are evaluated top to bottom: the first one that is true wins.
• ELSE is the default value if no condition holds. If you omit it and no branch matches, the result is NULL.
• END closes the expression. With AS you name the computed column.

Shorthand form

When you compare the same column against specific values, there is a shorter form:

SELECT name,
  CASE category
    WHEN 'peripherals' THEN 'accessory'
    WHEN 'displays'    THEN 'visualization'
    ELSE 'other'
  END AS type
FROM products;

Mind the order of the conditions. Since the first one that holds wins, the more specific branches must come before the more general ones.

Examples

Classify prices into readable tiers

SELECT name, price,
  CASE
    WHEN price < 50  THEN 'cheap'
    WHEN price < 200 THEN 'medium'
    ELSE 'expensive'
  END AS tier
FROM products
ORDER BY price;