Skip to content
Tutorials Center
My learning

Topics

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.

SQL
SELECT name,
       CASE WHEN price < 20 THEN 'cheap' ELSE 'regular' END AS band
FROM products;

Try it

SQL Searched CASE with several branches ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Simple CASE, CASE inside aggregates and in ORDER BY ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Syntax

SQL
-- 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()
SQL
-- 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 WHEN wins, so put the most specific condition first.
  • No match, no ELSE: NULL. Add an ELSE unless NULL is what you want.
  • Simple CASE cannot match NULL. CASE x WHEN NULL THEN ... never matches because x = NULL is not true. Use the searched form with WHEN 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. The FILTER clause is a shorter form where supported: see FILTER.
  • 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.
  • CASE in UPDATE lets one statement set different values per row: SET price = CASE WHEN stock = 0 THEN price * 0.8 ELSE price END.