Conditional expressions in GoogleSQL

GoogleSQL for Spanner 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

CASE expr

CASEexpr
WHENexpr_to_matchTHENresult
[...]
[ELSEelse_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.

Return Data Type

Supertype of result[, ...] and else_result.

Example

WITHNumbersAS(
SELECT90asA,2asBUNIONALL
SELECT50,8UNIONALL
SELECT60,6UNIONALL
SELECT50,10
)
SELECT
A,
B,
CASEA
WHEN90THEN'red'
WHEN50THEN'blue'
ELSE'green'
END
ASresult
FROMNumbers
/*------------------*
 | A | B | result |
 +------------------+
 | 90 | 2 | red |
 | 50 | 8 | blue |
 | 60 | 6 | green |
 | 50 | 10 | blue |
 *------------------*/

CASE

CASE
WHENconditionTHENresult
[...]
[ELSEelse_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.

Return Data Type

Supertype of result[, ...] and else_result.

Example

WITHNumbersAS(
SELECT90asA,2asBUNIONALL
SELECT50,6UNIONALL
SELECT20,10
)
SELECT
A,
B,
CASE
WHENA > 60THEN'red'
WHENB=6THEN'blue'
ELSE'green'
END
ASresult
FROMNumbers
/*------------------*
 | 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

SELECTCOALESCE('A','B','C')asresult
/*--------*
 | result |
 +--------+
 | A |
 *--------*/

SELECTCOALESCE(NULL,'B','C')asresult
/*--------*
 | 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
10ASA,
20ASB,
IF(10 < 20,'true','false')ASresult
/*------------------*
 | A | B | result |
 +------------------+
 | 10 | 20 | true |
 *------------------*/

SELECT
30ASA,
20ASB,
IF(30 < 20,'true','false')ASresult
/*------------------*
 | 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

SELECTIFNULL(NULL,0)asresult
/*--------*
 | result |
 +--------+
 | 0 |
 *--------*/

SELECTIFNULL(10,0)asresult
/*--------*
 | 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.

Return Data Type

Supertype of expr and expr_to_match.

Example

SELECTNULLIF(0,0)asresult
/*--------*
 | result |
 +--------+
 | NULL |
 *--------*/

SELECTNULLIF(10,0)asresult
/*--------*
 | result |
 +--------+
 | 10 |
 *--------*/