SQLDailyDrills

CASE

Choose a value per row, depending on conditions. It works like IF and IFS in Excel.

Syntax

CASE WHEN condition THEN value [WHEN … THEN …] ELSE value END
  • The first WHEN that is true wins. ELSE is optional; without it, the result is NULL.

Example

SELECT name, CASE WHEN unit_price < 3.00 THEN 'budget' ELSE 'standard' END AS band FROM products;

Each product with 'budget' or 'standard' as its band.

Tables used: products. Try it in a lesson below.

Common mistakes

  • Put the more specific condition first. The first true WHEN is the one that is used.
  • Without ELSE, rows that match no WHEN give NULL, and COUNT does not count NULL.

Practise it