CASE WHEN usage and examples

Overview

CASE WHEN evaluates conditions from top to bottom and returns the result of the first matching branch. If no branch matches and ELSE is omitted, the result is NULL. Keep branch result types compatible and put more specific predicates before broader ones.

Basic syntax

SELECT name, CASE WHEN amount >= 1000 THEN 'High' ELSE 'Normal' END AS level FROM orders;

Examples

CASE WHEN example

Label each order High or Normal according to whether amount reaches 1000.

Sample table

Table：orders

orders
name	amount
Alice	1200
Bob	900
Cindy	1500
David	700

Run result

result
name	level
Alice	High
Bob	Normal
Cindy	High
David	Normal

Common issues

Is ELSE required in CASE WHEN?

No, but rows that do not match any condition return NULL if ELSE is omitted.

Can CASE WHEN have multiple WHEN clauses?

Yes. SQL checks them from top to bottom and returns the first matching result.

Can CASE WHEN be used with GROUP BY?

Yes. It is often used to create buckets before aggregation.

Keep reading

COALESCE NULLIF IS NULL IFNULL CAST

CASE WHEN