GoogleSQL for BigQuery 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
. |
COALESCE
|
Produces the value of the first non- NULL
expression, if any,
otherwise NULL
. |
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
WITH
Numbers
AS
(
SELECT
90
as
A
,
2
as
B
UNION
ALL
SELECT
50
,
8
UNION
ALL
SELECT
60
,
6
UNION
ALL
SELECT
50
,
10
)
SELECT
A
,
B
,
CASE
A
WHEN
90
THEN
'red'
WHEN
50
THEN
'blue'
ELSE
'green'
END
AS
result
FROM
Numbers
/*------------------*
| A | B | result |
+------------------+
| 90 | 2 | red |
| 50 | 8 | blue |
| 60 | 6 | green |
| 50 | 10 | blue |
*------------------*/
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
WITH
Numbers
AS
(
SELECT
90
as
A
,
2
as
B
UNION
ALL
SELECT
50
,
6
UNION
ALL
SELECT
20
,
10
)
SELECT
A
,
B
,
CASE
WHEN
A
>
60
THEN
'red'
WHEN
B
=
6
THEN
'blue'
ELSE
'green'
END
AS
result
FROM
Numbers
/*------------------*
| A | B | result |
+------------------+
| 90 | 2 | red |
| 50 | 6 | blue |
| 20 | 10 | green |
*------------------*/
COALESCE
COALESCE
(
expr
[
,
...
]
)
Description
Returns the value of the first non- NULL
expression, if any, otherwise NULL
. The remaining expressions aren't evaluated. An input expression can be
any type. There may be multiple input expression types.
All input expressions must be implicitly coercible to a common supertype
.
Return Data Type
Supertype
of expr
[, ...].
Examples
SELECT
COALESCE
(
'A'
,
'B'
,
'C'
)
as
result
/*--------*
| result |
+--------+
| A |
*--------*/
SELECT
COALESCE
(
NULL
,
'B'
,
'C'
)
as
result
/*--------*
| result |
+--------+
| B |
*--------*/
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 |
*--------*/
