Conditional expressions

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