Return one of two values based on a boolean condition (alias for IIF).
IFF(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 IFF for concise two-way branching without writing out a full CASE expression. IFF is a synonym for IIF. Pick one spelling per codebase for consistency. ## Behavior - Returns NULL if both branches return NULL. - 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 IIF. 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 IFF(1 > 0, 'yes', 'no'); -- 'yes'-- Pass/fail classification
SELECT exam_id, IFF(score >= 60, 'PASS', 'FAIL') AS result FROM edu.catalog.exams;-- NULL condition evaluates to the false branch
SELECT IFF(NULL, 'true_branch', 'false_branch'); -- 'false_branch'-- Nest for multi-way branching (use CASE for clarity at scale)
SELECT IFF(grade = 'A', 4.0, IFF(grade = 'B', 3.0, 2.0)) AS gpa FROM edu.catalog.students;-- Compute indicator column for aggregation
SELECT SUM(IFF(status = 'active', 1, 0)) AS active_count FROM crm.catalog.accounts;