CASE WHEN

Functions
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

Filter by Database
SQL CASE WHEN Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Native all Both simple and searched CASE. Also supports IF(condition, true_val, false_val) and IIF() as a 2-arg shorthand.
PostgreSQL Native all Both simple and searched CASE. CASE can appear in SELECT, WHERE, ORDER BY, HAVING, and aggregate arguments.
SQL Server Native all Both simple and searched CASE. Also IIF(condition, true_val, false_val) since SQL Server 2012.
Oracle Native all Both simple and searched CASE. Legacy DECODE(expr, search, result, ...) function also works.
SQLite Native 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: Native in all listed versions PostgreSQL: Native in all listed versions SQL Server: Native in all listed versions Oracle: Native in all listed versions SQLite: Native in all listed versions

Per-Database Syntax & Notes

MySQL Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

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 Native syntax

Both simple and searched CASE fully supported.

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