SQL Reference ยท Scalar Functions
SQL IIF() function
Reference for SQL IIF(): a compact inline conditional in SQLite and SQL Server, how it maps to CASE, NULL conditions, and the IF() and DECODE() relatives.
IIF(condition, if_true, if_false) evaluates a condition and returns one of two values. It is a compact form of a two-branch CASE expression: handy for flags, labels and conditional arithmetic.
IIF is available in SQLite (3.32+) and SQL Server (2012+). It is not part of the SQL standard. PostgreSQL and Oracle do not have it; CASE works everywhere.
SELECT title, IIF(stock = 0, 'sold out', 'available') AS status FROM books;
Try it
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Press Run (or Ctrl + Enter) to see the result here.
Syntax
IIF(condition, value_if_true, value_if_false)
| Part | Description |
|---|---|
condition |
A boolean expression. |
value_if_true |
Returned when the condition is true. |
value_if_false |
Returned when the condition is false or unknown (NULL). |
Returns: one of the two values. It is equivalent to CASE WHEN condition THEN value_if_true ELSE value_if_false END.
Dialect differences
| Database | Support |
|---|---|
| SQLite | IIF(cond, a, b) since 3.32; also accepts more pairs: IIF(c1, a, c2, b, else). |
| SQL Server | IIF(cond, a, b) since 2012, translated internally to CASE; limited to 10 nested levels. |
| PostgreSQL | None. Use CASE WHEN. |
| MySQL | IF(cond, a, b) with the same meaning (the name IIF does not exist). |
| Oracle | None. Use CASE WHEN, or DECODE(expr, search, result, ..., default) for equality tests. |
-- MySQL SELECT IF(stock = 0, 'sold out', 'available') FROM books; -- Oracle SELECT DECODE(stock, 0, 'sold out', 'available') FROM books;
Usage notes
- Unknown is false. If the condition evaluates to
NULL, you get the third argument.IIF(stock > 5, ...)treats rows withNULLstock as "not greater". TestIS NULLfirst if that matters. - Types. The two result values should be of compatible types. SQL Server picks the type with higher precedence; SQLite allows mixing.
- Conditional aggregates.
SUM(IIF(c, 1, 0))counts rows that meetc;FILTERorCOUNT(CASE ...)are alternatives. See FILTER. - Many branches. Use CASE; nested
IIFcalls get hard to read quickly. - Portability. If code must run on several databases, use
CASEfrom the beginning. - Short-circuit. Only the chosen branch is evaluated in practice, but do not rely on it to avoid errors such as division by zero; use NULLIF.
Related
- CASE, COALESCE(), NULLIF(), FILTER clause
- Lessons: CASE, Calculated columns