Return one of two values based on a boolean condition.
IIF(condition, true_value, false_value)## Overview Evaluates a boolean condition and returns the true_value when the condition is true and the false_value when the condition is false or NULL. Use IIF for concise two-way branching without writing out a full CASE expression. IIF is a synonym for IFF. Pick one spelling per codebase for consistency. ## Behavior - NULL conditions take the false branch (not a NULL result). - true_value and false_value must be type-compatible; the result type is the common type. - Both branch expressions are analyzed for type checking but only one is evaluated per row. - Deterministic with respect to its arguments; side effect free. ## Compatibility - PG-compat alias for IFF. Equivalent to `CASE WHEN condition THEN true_value ELSE false_value END`.
| Name | Type | Description |
|---|---|---|
condition | Specifies the boolean expression to evaluate. NULL is treated as false. | |
true_value | Specifies the value returned when the condition is true. | |
false_value | Specifies the value returned when the condition is false or NULL. Must be type-compatible with true_value. |
-- Literal boolean
SELECT IIF(10 > 5, 'greater', 'not greater'); -- 'greater'-- Classify amounts
SELECT amount, IIF(amount > 1000, 'high', 'low') AS tier FROM fin.catalog.transactions;-- NULL condition takes the false branch
SELECT IIF(NULL, 'yes', 'no'); -- 'no'-- Build an indicator column for aggregation
SELECT SUM(IIF(status = 'active', 1, 0)) AS active_count FROM crm.catalog.accounts;-- Avoid deep nesting: prefer CASE for three or more branches
SELECT IIF(score >= 90, 'A', IIF(score >= 80, 'B', 'C')) AS grade FROM edu.catalog.students;