IIF / DECODE / CONDITIONAL SHORTHANDFunctions
Buy Me A Coffee

Compact conditional expressions that return one of two or more values based on a condition, without the full verbosity of CASE WHEN. Includes IIF (SQL Server, Access), DECODE (Oracle), and IF (MySQL).

Conditional logic in SQL: IF(condition, true_val, false_val) in MySQL, DECODE(expr, val1, result1, ...) in Oracle. CASE is more portable across databases and supports multiple conditions. NVL2 and IIF provide shorthand for common patterns.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✓ Supported 3.23 IF(condition, true_val, false_val) is the primary ternary conditional. IFNULL(expr, fallback) is shorthand for the NULL-check case. IIF is not supported. DECODE is not supported. All three are non-standard; the portable equivalent is CASE WHEN ... END.
PostgreSQL ✗ Not Supported — PostgreSQL does not support IIF, DECODE, or IF as conditional functions. The standard CASE WHEN ... END expression covers all use cases. COALESCE and NULLIF handle the common NULL-check patterns. No shorthand alternative — use CASE.
SQL Server ✓ Supported 2012 IIF(condition, true_val, false_val) added in SQL Server 2012 (ported from Microsoft Access). It is syntactic sugar for CASE WHEN condition THEN true_val ELSE false_val END — the query optimizer treats them identically. CHOOSE(index, val1, val2, ...) also added in 2012, returns the value at the 1-based index position. DECODE is not supported.
Oracle ✓ Supported 7 DECODE(expr, search1, result1, search2, result2, ..., default) is Oracle's proprietary multi-branch conditional, available since Oracle 7. It handles NULLs specially — DECODE treats two NULLs as equal (unlike = comparison). IIF is not supported. Oracle 9i+ supports the standard CASE WHEN expression alongside DECODE. NVL(expr, default) is the Oracle shorthand for NULL checking (equivalent to COALESCE with two args).
SQLite ✗ Not Supported — SQLite does not support IIF, DECODE, or IF as functions. CASE WHEN ... END is the only conditional expression. COALESCE and IFNULL (SQLite does support IFNULL as an alias for COALESCE with two arguments) handle NULL checks.

Details

The fully portable form is always CASE WHEN ... THEN ... ELSE ... END — supported by every engine since early versions. IIF exists in SQL Server (2012+) and SQLite (3.32.0+) but nowhere else. Oracle's DECODE is unique: it treats NULL = NULL as true (unlike every other comparison in SQL), making it subtly different from CASE WHEN col IS NULL. MySQL's IF() function is only available in MySQL — do not use it in cross-engine schemas. For NULL-check shorthand: COALESCE is the most portable (SQL standard), NVL is Oracle-only, IFNULL works in MySQL and SQLite, ISNULL works in SQL Server.

Standard Syntax

-- SQL Server / Access: SELECT IIF(salary > 50000, 'Senior', 'Junior') AS grade FROM employees; -- Oracle: SELECT DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown') AS label FROM accounts; -- MySQL: SELECT IF(salary > 50000, 'Senior', 'Junior') AS grade FROM employees;

Version Support

MySQL: Since 3.23 PostgreSQL: Not supported SQL Server: Since 2012 Oracle: Since 7 SQLite: Not supported

Per-Database Syntax & Notes

MySQL

IF(condition, true_val, false_val) is the primary ternary conditional. IFNULL(expr, fallback) is shorthand for the NULL-check case. IIF is not supported. DECODE is not supported. All three are non-standard; the portable equivalent is CASE WHEN ... END.

-- IF (ternary): SELECT IF(salary > 50000, 'Senior', 'Junior') AS grade FROM employees; -- Nested IF (equivalent to CASE WHEN): SELECT IF(score >= 90, 'A', IF(score >= 80, 'B', IF(score >= 70, 'C', 'F'))) AS grade FROM students; -- IFNULL (NULL-safe shorthand): SELECT IFNULL(nickname, full_name) AS display_name FROM users; -- Portable CASE equivalent: SELECT CASE WHEN salary > 50000 THEN 'Senior' ELSE 'Junior' END AS grade FROM employees;

PostgreSQL

PostgreSQL does not support IIF, DECODE, or IF as conditional functions. The standard CASE WHEN ... END expression covers all use cases. COALESCE and NULLIF handle the common NULL-check patterns. No shorthand alternative — use CASE.

-- CASE WHEN (the only form; IIF/IF/DECODE not available): SELECT CASE WHEN salary > 50000 THEN 'Senior' ELSE 'Junior' END AS grade FROM employees; -- Multi-branch (DECODE equivalent): SELECT CASE status WHEN 'A' THEN 'Active' WHEN 'I' THEN 'Inactive' ELSE 'Unknown' END AS label FROM accounts; -- NULL check (IFNULL equivalent): SELECT COALESCE(nickname, full_name) AS display_name FROM users;

SQL Server

IIF(condition, true_val, false_val) added in SQL Server 2012 (ported from Microsoft Access). It is syntactic sugar for CASE WHEN condition THEN true_val ELSE false_val END — the query optimizer treats them identically. CHOOSE(index, val1, val2, ...) also added in 2012, returns the value at the 1-based index position. DECODE is not supported.

-- IIF (SQL Server 2012+): SELECT IIF(salary > 50000, 'Senior', 'Junior') AS grade FROM employees; -- Nested IIF: SELECT IIF(score >= 90, 'A', IIF(score >= 80, 'B', IIF(score >= 70, 'C', 'F'))) AS grade FROM students; -- CHOOSE (index into list): SELECT CHOOSE(priority, 'Low', 'Medium', 'High', 'Critical') AS priority_label FROM tickets; -- CHOOSE(1,...) = 'Low', CHOOSE(2,...) = 'Medium', etc. -- ISNULL (NULL check shorthand): SELECT ISNULL(nickname, full_name) AS display_name FROM users;

Oracle

DECODE(expr, search1, result1, search2, result2, ..., default) is Oracle's proprietary multi-branch conditional, available since Oracle 7. It handles NULLs specially — DECODE treats two NULLs as equal (unlike = comparison). IIF is not supported. Oracle 9i+ supports the standard CASE WHEN expression alongside DECODE. NVL(expr, default) is the Oracle shorthand for NULL checking (equivalent to COALESCE with two args).

-- DECODE (Oracle proprietary): SELECT DECODE(status, 'A', 'Active', 'I', 'Inactive', 'Unknown') AS label FROM accounts; -- DECODE with NULL match (treats NULL = NULL as true): SELECT DECODE(region, NULL, 'No Region', region) AS region_label FROM customers; -- DECODE as ternary: SELECT DECODE(SIGN(salary - 50000), 1, 'Senior', 'Junior') AS grade FROM employees; -- NVL (NULL shorthand): SELECT NVL(nickname, full_name) AS display_name FROM users; -- Standard CASE (preferred for new code, 9i+): SELECT CASE WHEN salary > 50000 THEN 'Senior' ELSE 'Junior' END AS grade FROM employees;

SQLite

SQLite does not support IIF, DECODE, or IF as functions. CASE WHEN ... END is the only conditional expression. COALESCE and IFNULL (SQLite does support IFNULL as an alias for COALESCE with two arguments) handle NULL checks.

-- CASE WHEN (standard form): SELECT CASE WHEN salary > 50000 THEN 'Senior' ELSE 'Junior' END AS grade FROM employees; -- IFNULL (SQLite does support this one): SELECT IFNULL(nickname, full_name) AS display_name FROM users; -- IIF was added as an alias for CASE WHEN in SQLite 3.32.0: SELECT IIF(salary > 50000, 'Senior', 'Junior') AS grade FROM employees;