SQLearnAcademy
←Function reference
ConditionalSQL standard

CASE

Returns different values depending on conditions — “if… then… else” in SQL.

Syntax

CASE WHEN <condition> THEN <result> … ELSE <otherwise> END
CASE <expression> WHEN <value> THEN <result> … ELSE <otherwise> END

It's an expression rather than a function, but you write it in the same places as functions. Conditions are checked from top to bottom, and the first true one wins; if none matches, ELSE is used, and without it — NULL. The second form is shorter: it compares one expression against a list of values.

Data for the examples

employees

namedepartmentsalarybonusphone
Emmabackend1200300+44 7700 900123
Liambackend1000NULLNULL
Oliviafrontend1350150+44 7700 900456
Noahfrontend950NULL+44 7700 900789
Sophiadesign1200200NULL

These tables already exist in the sandbox — the examples below use them. Want to try this in your own database? Copy the SQL.

Salary level
SELECT name, salary,
CASE
WHEN salary >= 1200 THEN 'high'
WHEN salary >= 1000 THEN 'medium'
ELSE 'low'
END AS level
FROM employees;
namesalarylevel
Emma1200high
Liam1000medium
Olivia1350high
Noah950low
Sophia1200high
The short form — a list of values
SELECT name,
CASE department
WHEN 'backend' THEN 'Server'
WHEN 'frontend' THEN 'Web'
ELSE 'Other'
END AS team
FROM employees;
nameteam
EmmaServer
LiamServer
OliviaWeb
NoahWeb
SophiaOther
Counting by condition in each departmentAdvanced
SELECT department,
COUNT(*) AS people,
SUM(CASE WHEN bonus IS NULL THEN 0 ELSE 1 END) AS with_bonus
FROM employees
GROUP BY department
ORDER BY department;
departmentpeoplewith_bonus
backend21
design11
frontend21

For each row, CASE gives 1 or 0, and SUM adds them up — that's the number of people with a bonus.

Watch out

The first true condition wins. Put WHEN salary >= 1000 above WHEN salary >= 1200, and nobody gets the high level.

The short form compares with =, so CASE bonus WHEN NULL THEN … never fires: NULL isn't equal even to NULL. Use the full form: CASE WHEN bonus IS NULL THEN ….

All branches must return a compatible type: CASE WHEN … THEN 1 ELSE 'none' END throws invalid input syntax for type integer.

Related functions

COALESCENULLIFGREATEST