CASE expressions

2 min read 6 runnable examples Quiz included

CASE is SQL's if-then-else. It checks conditions in order and returns the value for the first one that's true:

SQL

SELECT
  name,
  price,
  CASE
    WHEN price < 50  THEN 'budget'
    WHEN price < 150 THEN 'mid-range'
    ELSE 'premium'
  END AS price_band
FROM products
ORDER BY price;

Conditions are checked top to bottom, and the first true one wins. A product priced 40 is "budget", even though it's also under 150.
ELSE catches everything else. Without an ELSE, unmatched rows get NULL.
END closes the expression, and the alias names the new column.

Simple CASE

When every test compares the same value for equality, there's a shorter form:

SQL

SELECT
  order_id,
  status,
  CASE status
    WHEN 'delivered'  THEN 'Done'
    WHEN 'shipped'    THEN 'On its way'
    WHEN 'processing' THEN 'Being packed'
    ELSE 'Needs attention'
  END AS for_customer
FROM orders;

Stock warnings

SQL

SELECT name, stock,
  CASE
    WHEN stock = 0  THEN 'Out of stock'
    WHEN stock < 20 THEN 'Low: reorder'
    ELSE 'OK'
  END AS stock_status
FROM products
ORDER BY stock;

Counting conditionally

CASE inside SUM counts just the rows you care about, which gives several counts in a single pass over the table:

SQL

SELECT
  COUNT(*) AS total,
  SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END)                AS delivered,
  SUM(CASE WHEN status IN ('shipped', 'processing') THEN 1 ELSE 0 END) AS in_progress,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END)                AS cancelled
FROM orders;

Grouping into bands

SQL

SELECT
  CASE
    WHEN salary >= 150000 THEN '150k and up'
    WHEN salary >= 100000 THEN '100k to 150k'
    ELSE 'under 100k'
  END AS salary_band,
  COUNT(*) AS staff
FROM employees
GROUP BY salary_band
ORDER BY MIN(salary) DESC;

A custom sort order

Alphabetical isn't always the order that makes sense. This puts the orders that need action first:

SQL

SELECT order_id, status, order_date
FROM orders
ORDER BY
  CASE status
    WHEN 'processing' THEN 1
    WHEN 'shipped'    THEN 2
    WHEN 'delivered'  THEN 3
    ELSE 4
  END,
  order_date;

Finished this lesson?Mark it complete to earn +20 XP and move your progress on.