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. |