SQL Reference ยท Operators and Predicates
SQL CASE expression
Reference for the SQL CASE expression: searched and simple forms, ELSE and NULL results, CASE in ORDER BY, GROUP BY and aggregates, and dialect notes.
CASE is SQL's conditional expression, like if / else if / else in other languages. It checks conditions in order and returns the value for the first one that is true. It is an expression, not a statement, so it can appear anywhere a value can: in SELECT, WHERE, ORDER BY, GROUP BY, inside aggregates and in UPDATE ... SET.
There are two forms. The searched form tests any boolean conditions. The simple form compares one expression against a list of values.
SELECT name, CASE WHEN price < 20 THEN 'cheap' ELSE 'regular' END AS band FROM products;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
-- searched CASE WHEN condition1 THEN result1 [WHEN condition2 THEN result2 ...] [ELSE default_result] END -- simple CASE expression WHEN value1 THEN result1 [WHEN value2 THEN result2 ...] [ELSE default_result] END
| Part | Description |
|---|---|
WHEN condition |
In the searched form, a boolean expression. Branches are tried top to bottom and the first true one wins. |
expression / WHEN value |
In the simple form, expression is compared to each value with =. |
THEN result |
The value returned when the branch matches. |
ELSE default_result |
Optional. Returned when no branch matches. |
END |
Required terminator. |
Returns: the matching result, or NULL if nothing matches and there is no ELSE. All results should have compatible types.
Dialect differences
| Feature | SQLite | PostgreSQL | MySQL | SQL Server | Oracle |
|---|---|---|---|---|---|
Searched and simple CASE |
Yes | Yes | Yes | Yes | Yes |
CASE as a statement in procedures |
No | PL/pgSQL has its own CASE |
Yes (END CASE) |
No (use IF) |
PL/SQL CASE |
| Mixed result types (number and text) | Allowed | Error | Converted | Error or conversion by type precedence | Error |
| Shorthand functions | IIF(), COALESCE() |
COALESCE(), NULLIF() |
IF(), IFNULL() |
IIF(), ISNULL() |
DECODE(), NVL() |
-- MySQL: IF() and the CASE statement in a stored program SELECT IF(stock = 0, 'sold out', 'in stock') FROM products; -- Oracle: DECODE is an older compact form of simple CASE SELECT DECODE(status, 'pending', 1, 'shipped', 2, 3) FROM orders;
Usage notes
- Order matters. The first true
WHENwins, so put the most specific condition first. - No match, no
ELSE:NULL. Add anELSEunlessNULLis what you want. - Simple
CASEcannot matchNULL.CASE x WHEN NULL THEN ...never matches becausex = NULLis not true. Use the searched form withWHEN x IS NULL. - Avoid relying on evaluation order to dodge errors such as division by zero in a skipped branch; engines may fold constant expressions early. Use
NULLIF()for that. - Conditional aggregation:
SUM(CASE WHEN ... THEN 1 ELSE 0 END)counts rows meeting a condition and is the portable way to pivot data. TheFILTERclause is a shorter form where supported: seeFILTER. - Custom sort orders go in
ORDER BY CASE ..., as in the example above. - Keep result types consistent. Mixing numbers and text works in SQLite but fails or converts silently elsewhere.
CASEinUPDATElets one statement set different values per row:SET price = CASE WHEN stock = 0 THEN price * 0.8 ELSE price END.
Related
IIF(),COALESCE(),NULLIF(),FILTER,ORDER BY- Lessons: CASE expressions, Pivot tables