Conditional expressions impose constraints on the evaluation order of their
inputs. In essence, they are evaluated left to right, with short-circuiting, and
only evaluate the output value that was chosen. In contrast, all inputs to
regular functions are evaluated before calling the function. Short-circuiting in
conditional expressions can be exploited for error handling or performance
tuning.
| Syntax |
Input Data Types |
Result Data Type |
Description |
CASE expr
WHEN value THEN result
[WHEN ...]
[ELSE else_result]
END |
expr and value: Any type |
result and else_result: Supertype of input
types. |
Compares expr to value of each successive WHEN
clause and returns the first result where this comparison returns true. The
remaining WHEN clauses and else_result are not
evaluated. If the
expr = value comparison returns false or NULL for
all WHEN clauses, returns
else_result if present; if not present, returns NULL.
expr and value expressions
must be implicitly coercible to a common supertype; equality comparisons are
done on coerced values. result and else_result
expressions must be coercible to a common supertype. |
CASE
WHEN cond1 THEN result
[WHEN cond2...]
[ELSE else_result]
END |
cond: BOOL |
result and else_result: Supertype of input
types. |
Evaluates condition cond of each successive WHEN
clause and returns the first result where the condition is true; any remaining
WHEN clauses and else_result are not evaluated. If all
conditions are false or NULL, returns
else_result if present; if not present, returns
NULL. result and else_result
expressions must be implicitly coercible to a common supertype. |
| COALESCE(expr1, ..., exprN) |
Any type |
Supertype of input types |
Returns the value of the first non-null expression. The remaining
expressions are not evaluated. All input expressions must be implicitly
coercible to a common supertype. |
| IF(cond, true_result, else_result) |
cond: BOOL |
true_result and else_result: Any type. |
If cond is true, returns true_result, else returns
else_result. else_result is not evaluated if
cond is true. true_result is not evaluated if
cond is false or NULL. true_result and
else_result must be coercible to a common supertype. |
| IFNULL(expr, null_result) |
Any type |
Any type or supertype of input types. |
If expr is NULL, return null_result. Otherwise,
return expr. If expr is not NULL,
null_result is not evaluated. expr and
null_result must be implicitly coercible to a common
supertype. Synonym for COALESCE(expr, null_result). |
| NULLIF(expression, expression_to_match) |
Any type T or subtype of T |
Any type T or subtype of T |
Returns NULL if expression = expression_to_match
is true, otherwise returns expression. expression and
expression_to_match must be implicitly coercible to a common
supertype; equality comparison is done on coerced values. |
