CASE WHENFunctions
Buy Me A Coffee

Conditional expression returning different values based on evaluated conditions.

The SQL conditional expression, supporting searched CASE (WHEN condition THEN result) and simple CASE (WHEN expression = value THEN result). Used for pivot-like transformations, conditional aggregation, and business rule encoding. Evaluate NULLIF and COALESCE first for simpler cases.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported all Both simple and searched CASE. Also supports IF(condition, true_val, false_val) and IIF() as a 2-arg shorthand.
PostgreSQL ✓ Supported all Both simple and searched CASE. CASE can appear in SELECT, WHERE, ORDER BY, HAVING, and aggregate arguments.
SQL Server ✓ Supported all Both simple and searched CASE. Also IIF(condition, true_val, false_val) since SQL Server 2012.
Oracle ✓ Supported all Both simple and searched CASE. Legacy DECODE(expr, search, result, ...) function also works.
SQLite ✓ Supported all Both simple and searched CASE fully supported.

Details

CASE is SQL-standard and works identically across all major engines. Dialect-specific shorthands (IF, IIF, DECODE) exist but are not portable.

Standard Syntax

-- Searched CASE (most common) SELECT CASE WHEN score >= 90 THEN 'A' WHEN score >= 80 THEN 'B' ELSE 'C' END AS grade FROM results; -- Simple CASE SELECT CASE status WHEN 1 THEN 'Active' WHEN 0 THEN 'Inactive' END FROM tbl;

Version Support

MySQL: Since all PostgreSQL: Since all SQL Server: Since all Oracle: Since all SQLite: Since all

Per-Database Syntax & Notes

MySQL

Both simple and searched CASE. Also supports IF(condition, true_val, false_val) and IIF() as a 2-arg shorthand.

SELECT CASE WHEN x > 0 THEN 'pos' ELSE 'neg' END FROM tbl; SELECT IF(x > 0, 'pos', 'neg') FROM tbl;

PostgreSQL

Both simple and searched CASE. CASE can appear in SELECT, WHERE, ORDER BY, HAVING, and aggregate arguments.

SELECT CASE WHEN x > 0 THEN 'pos' ELSE 'neg' END FROM tbl;

SQL Server

Both simple and searched CASE. Also IIF(condition, true_val, false_val) since SQL Server 2012.

SELECT CASE WHEN x > 0 THEN 'pos' ELSE 'neg' END FROM tbl; SELECT IIF(x > 0, 'pos', 'neg') FROM tbl;

Oracle

Both simple and searched CASE. Legacy DECODE(expr, search, result, ...) function also works.

SELECT CASE WHEN x > 0 THEN 'pos' ELSE 'neg' END FROM tbl; SELECT DECODE(x, 1, 'one', 2, 'two', 'other') FROM tbl;

SQLite

Both simple and searched CASE fully supported.

SELECT CASE WHEN x > 0 THEN 'pos' ELSE 'neg' END FROM tbl;