CASE

Definition

CASE WHEN ... THEN ... ELSE ... END is SQL's conditional expression — it evaluates a series of conditions in order and returns the value tied to the first one that's TRUE, falling back to ELSE (or NULL if ELSE is omitted) if none match.

Running example — a products(id, name, category, price, stock) table, where stock can be NULL (not yet counted):

sql
SELECT
  name,
  price,
  CASE
    WHEN price < 300 THEN 'Budget'
    WHEN price <= 1000 THEN 'Mid'
    ELSE 'Premium'
  END AS tier
FROM products;

Conditions are checked TOP TO BOTTOM, and the FIRST match wins — for a price of exactly 300, the first branch (price < 300) is false, so it falls through to the second (price <= 1000, true) and returns 'Mid'. This ordering-matters behavior is easy to get wrong if branches aren't written from most-specific to least-specific (or, as here, in a logical increasing order).

Edge Cases and Pitfalls

• CASE can appear almost anywhere a value is expected — in SELECT, WHERE, ORDER BY, even inside another function call — because it's an EXPRESSION, not a separate statement.
• There's also a "simple CASE" form, CASE column WHEN val1 THEN ... WHEN val2 THEN ... END, which only tests equality against one column — more concise than the full WHEN condition THEN form when every branch is a plain equality check on the same column.
• If no WHEN branch matches and there's no ELSE, the result is NULL — not an error — which can silently produce unexpected NULLs if a case wasn't anticipated; including an explicit ELSE is good defensive practice.

Key Takeaways

• CASE WHEN...THEN...ELSE...END evaluates conditions top-to-bottom and returns the first match's value.
• It's a value-producing expression usable almost anywhere, not a standalone statement.
• Omitting ELSE silently produces NULL for unmatched rows — usually worth avoiding with an explicit ELSE.