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 |
*--------*/