Conditional expressions in Dataflow SQL

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.