IIf (inline if / conditional value)

Syntax

IIf(<condition>, <value-if-true>, <value-if-false>)

π name, IIf(score >= 50, "pass", "fail") → result (Results)

Description

IIf chooses between two values based on a condition — the spreadsheet IF. If the condition is true it returns the first value, otherwise the second. Use it to derive labels, flags, or category columns from a test.

Technical Description

IIf(condition, trueValue, falseValue) → ANY. The first argument must evaluate to a BOOLEAN (else an evaluation error); a NULL condition returns NULL. Returns trueValue when true, else falseValue. PURE, DETERMINISTIC.

IIf short-circuits: the condition is evaluated, then only the branch it selects — the other is never evaluated (and a NULL condition evaluates neither). That is what makes IIf usable as a guard: the untaken branch may be an expression that would fail on those rows, such as converting a value that is not a number. Switch, Choose, Nz and Coalesce are lazy in the same way, and these five are the only built-ins that are — every other function evaluates all its arguments.

IIf folds into a backend CASE on every SQL dialect, so a query over a database source pushes it down. It is written CASE WHEN c THEN a WHEN NOT (c) THEN b END rather than with an ELSE: a NULL condition matches neither arm and the CASE is NULL, which is IIf's answer, where an ELSE would take the false branch. The condition folds when it is written as one — a comparison, a null test, or a connective over them — so IIf(active = true, …) folds where IIf(active, …) over a boolean column is evaluated in-engine. MongoDB, which has no CASE, evaluates it in-engine.

Examples

Pass/fail label:

π name, IIf(score >= 50, "pass", "fail") → result (Results)

π name, IIf(score ≥ 50, "pass", "fail") → result (Results)

PROJECT name, IIf(score >= 50, "pass", "fail") -> result (Results)

Bucket a numeric flag:

π order_id, IIf(amount > 1000, "large", "small") → size (Orders)

PROJECT order_id, IIf(amount > 1000, "large", "small") -> size (Orders)

Default a display value:

π order_id, IIf(active = true, status, "inactive") → display_status (Accounts)

PROJECT order_id, IIf(active = true, status, "inactive") -> display_status (Accounts)

A missing value in one branch — the result keeps the other branch's type:

π order_id, IIf(status = "cancelled", NULL, amount) → billable (Orders)

PROJECT order_id, IIf(status = "cancelled", NULL, amount) -> billable (Orders)

Count only some rows, SQL's portable aggregate filter:

γ region, COUNT(IIf(status = "shipped", order_id, NULL)) → shipped (Orders)

GROUP region, COUNT(IIf(status = "shipped", order_id, NULL)) -> shipped (Orders)

Limitations

The condition must be BOOLEAN. The result type is the branches' common type, ANY when they differ; a NULL branch takes the other's. A NULL condition yields NULL — neither branch is evaluated in that case.

Alternatives

Nz / Coalesce specifically for substituting a value when something is NULL.

SQL names

MySQL's and a spreadsheet's IF(c, a, b) is IIf(c, a, b), except that a NULL condition gives NULL here where IF gives b. SQL's CASE WHEN c THEN a ELSE b END is written as it is (CASE), and takes the ELSE for a NULL condition as SQL's does: it is Switch(c, a, b) rather than IIf(c, a, b). There is no IF function.

See Also

CASE, switch, choose, nz, coalesce, isnull, comparison

Notes

For "replace NULL with a default" the dedicated Nz/Coalesce read more clearly than IIf(IsNull(x), default, x).