SQLite 3 reference · Conditional and Type Functions

SQLite IIF() Function

Return one value if a condition is true and another if it's false: a short form of CASE WHEN … THEN … ELSE … END.

SQLite 3 Since 3.32.0

Create a free account to save this page, take notes and track your progress. Sign in

Syntax

iif(condition, X, Y)  |  iif(cond1, X1, cond2, X2, ..., else)  |  if(...)

Parameter	Description
`condition`	An expression that is true (non-zero) or false.
`X`	The result when true.
`Y` optional	The result when false; `NULL` if omitted.

X or Y.

Two outcomesTry it yourself

SELECT name, stock, iif(stock > 0, 'in stock', 'sold out') AS availability
FROM products
WHERE product_id IN (1, 3);

Output

name | stock | availability
SQL for Beginners | 40 | in stock
The Pragmatic Programmer | 0 | sold out

Several conditions (3.49+)Try it yourself

SELECT name, price,
       iif(price < 20, 'cheap', price < 100, 'mid', 'premium') AS tier
FROM products
WHERE product_id IN (8, 4, 6);

Output

name | price | tier
Mechanical Keyboard | 89 | mid
4K Monitor | 329 | premium
Coffee Mug | 12 | cheap

Version	Change
SQLite 3.32.0	Added
SQLite 3.48.0	The `else` value may be left out, and `if()` works as another name.
SQLite 3.49.0	Accepts any number of condition/value pairs.

CASE · Choose a value from conditions: CASE WHEN cond THEN result … ELSE default END. Works in SELECT, WHERE and ORDER BY.
COALESCE() · Return the first argument that isn't NULL. The standard way to replace NULL with a default value.
NULLIF() · Return NULL if two values are equal, otherwise the first value. Often used to avoid division by zero.
SQL CASE · tutorial