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