SQLite 3 reference · Conditional and Type Functions
SQLite CASE Expression
Choose a value from conditions: CASE WHEN cond THEN result … ELSE default END. Works in SELECT, WHERE and ORDER BY.
SQLite 3
Syntax
CASE WHEN cond1 THEN r1 [WHEN ...] [ELSE default] END | CASE expr WHEN v1 THEN r1 ... END
Parameters
| Parameter | Description |
|---|---|
cond / v |
Conditions to test, or values to compare expr with, checked in order. |
ELSE optional |
The result when nothing matches; NULL if omitted. |
Return value
The result of the first matching branch.
Examples
SELECT name, salary,
CASE
WHEN salary >= 200000 THEN 'executive'
WHEN salary >= 120000 THEN 'senior'
ELSE 'staff'
END AS band
FROM employees
WHERE employee_id IN (1, 4, 9);
Output
name | salary | band Sofia Reyes | 250000 | executive Jonas Berg | 140000 | senior Emma Novak | 88000 | staff
SELECT status, count(*) AS orders
FROM orders
GROUP BY status
ORDER BY CASE status WHEN 'pending' THEN 1 WHEN 'shipped' THEN 2 ELSE 3 END;
Output
status | orders pending | 2 shipped | 2 cancelled | 1 delivered | 7
In other languages
CASE· PostgreSQL
Related
IIF()· Return one value if a condition is true and another if it's false: a short form of CASE WHEN … THEN … ELSE … END.COALESCE()· Return the first argument that isn't NULL. The standard way to replace NULL with a default value.- SQL CASE · tutorial