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.