GoogleSQL for Bigtable supports conditional expressions. 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.
Expression list
| Name | Summary |
|---|---|
CASE expr | Compares the given expression to each successive WHEN clause and produces the first result where the values are equal. |
CASE | Evaluates the condition of each successive WHEN clause and produces the first result where the condition evaluates to TRUE. |
IF | If an expression evaluates to TRUE, produces a specified result, otherwise produces the evaluation for an else result. |
IFNULL | If an expression evaluates to NULL, produces a specified result, otherwise produces the expression. |
NULLIF | Produces NULL if the first expression that matches another evaluates to TRUE, otherwise returns the first expression. |
CASE expr
CASE expr WHEN expr_to_match THEN result [ ... ] [ ELSE else_result ] END Description
Compares expr to expr_to_match of each successive WHEN clause and returns the first result where this comparison evaluates to TRUE. The remaining WHEN clauses and else_result aren't evaluated.
If the expr = expr_to_match comparison evaluates to FALSE or NULL for all WHEN clauses, returns the evaluation of else_result if present; if else_result isn't present, then returns NULL.
Consistent with equality comparisons elsewhere, if both expr and expr_to_match are NULL, then expr = expr_to_match evaluates to NULL, which returns else_result. If a CASE statement needs to distinguish a NULL value, then the alternate CASE syntax should be used.
expr and expr_to_match can be any type. They must be implicitly coercible to a common supertype; equality comparisons are done on coerced values. There may be multiple result types. result and else_result expressions must be coercible to a common supertype.
This expression supports specifying collation.
Return Data Type
Supertype of result[, ...] and else_result.
Example
SELECT CASE MAP_KEYS(cell_plan)[0] WHEN b'data_plan_01gb' THEN 'Small data plan' WHEN b'data_plan_05gb' THEN 'Large data plan' ELSE 'Unknown data plan END AS result FROM test_table LIMIT 2 /*-----------------+ | result | +-----------------+ | Small data plan | | Large data plan | +-----------------*/ CASE
CASE WHEN condition THEN result [ ... ] [ ELSE else_result ] END Description
Evaluates the condition of each successive WHEN clause and returns the first result where the condition evaluates to TRUE; any remaining WHEN clauses and else_result aren't evaluated.
If all conditions evaluate to FALSE or NULL, returns evaluation of else_result if present; if else_result isn't present, then returns NULL.
For additional rules on how values are evaluated, see the three-valued logic table in Logical operators.
condition must be a boolean expression. There may be multiple result types. result and else_result expressions must be implicitly coercible to a common supertype.
This expression supports specifying collation.
Return Data Type
Supertype of result[, ...] and else_result.
Example
SELECT CASE WHEN MAP_KEYS(cell_plan)[0] != b'data_plan_01gb' THEN 'Small data plan' WHEN MAP_KEYS(cell_plan)[0] != b'data_plan_05gb' THEN 'Large data plan' ELSE 'Unknown data plan' END AS result FROM test_table LIMIT 2 /*-----------------+ | result | +-----------------+ | Large data plan | | Small data plan | +-----------------*/ IF
IF(expr, true_result, else_result) Description
If expr evaluates to TRUE, returns true_result, else returns the evaluation for else_result. else_result isn't evaluated if expr evaluates to TRUE. true_result isn't evaluated if expr evaluates to FALSE or NULL.
expr must be a boolean expression. true_result and else_result must be coercible to a common supertype.
Return Data Type
Supertype of true_result and else_result.
Examples
SELECT 10 AS A, 20 AS B, IF(10 < 20, 'true', 'false') AS result /*------------------+ | A | B | result | +------------------+ | 10 | 20 | true | +------------------*/ SELECT 30 AS A, 20 AS B, IF(30 < 20, 'true', 'false') AS result /*------------------+ | A | B | result | +------------------+ | 30 | 20 | false | +------------------*/ IFNULL
IFNULL(expr, null_result) Description
If expr evaluates to NULL, returns null_result. Otherwise, returns expr. If expr doesn't evaluate to NULL, null_result isn't evaluated.
expr and null_result can be any type and must be implicitly coercible to a common supertype. Synonym for COALESCE(expr, null_result).
Return Data Type
Supertype of expr or null_result.
Examples
SELECT IFNULL(NULL, 0) as result /*--------+ | result | +--------+ | 0 | +--------*/ SELECT IFNULL(10, 0) as result /*--------+ | result | +--------+ | 10 | +--------*/ NULLIF
NULLIF(expr, expr_to_match) Description
Returns NULL if expr = expr_to_match evaluates to TRUE, otherwise returns expr.
expr and expr_to_match must be implicitly coercible to a common supertype, and must be comparable.
This expression supports specifying collation.
Return Data Type
Supertype of expr and expr_to_match.
Example
SELECT NULLIF(0, 0) as result /*--------+ | result | +--------+ | NULL | +--------*/ SELECT NULLIF(10, 0) as result /*--------+ | result | +--------+ | 10 | +--------*/