Conditional expressions
GoogleSQL for BigQuery supports conditional expressions.Conditional expressions impose constraints on the evaluation order of theirinputs. In essence, they are evaluated left to right, with short-circuiting, andonly evaluate the output value that was chosen. In contrast, all inputs toregular functions are evaluated before calling the function. Short-circuiting inconditional expressions can be exploited for error handling or performancetuning.
Expression list
| Name | Summary |
|---|---|
CASE expr | Compares the given expression to each successiveWHEN clause and produces the first result where the values are equal. |
CASE | Evaluates the condition of each successiveWHEN clause and produces the first result where the condition evaluates toTRUE. |
COALESCE | Produces the value of the first non-NULL expression, if any, otherwiseNULL. |
IF | If an expression evaluates toTRUE, produces a specified result, otherwise produces the evaluation for anelse result. |
IFNULL | If an expression evaluates toNULL, produces a specified result, otherwise produces the expression. |
NULLIF | ProducesNULL if the first expression that matches another evaluates toTRUE, otherwise returns the first expression. |
CASE expr
CASEexprWHENexpr_to_matchTHENresult[...][ELSEelse_result]ENDDescription
Comparesexpr toexpr_to_match of each successiveWHEN clause and returnsthe first result where this comparison evaluates toTRUE. The remainingWHENclauses andelse_result aren't evaluated.
If theexpr = expr_to_match comparison evaluates toFALSE orNULL for allWHEN clauses, returns the evaluation ofelse_result if present; ifelse_result isn't present, then returnsNULL.
Consistent withequality comparisons elsewhere, if bothexpr andexpr_to_match areNULL, thenexpr = expr_to_match evaluates toNULL, which returnselse_result. If a CASE statement needs to distinguish aNULL value, then the alternateCASE syntax should be used.
expr andexpr_to_match can be any type. They must be implicitlycoercible to a commonsupertype; equality comparisons aredone on coerced values. There may be multipleresult types.result andelse_result expressions must be coercible to a common supertype.
This expression supports specifyingcollation.
Return Data Type
Supertype ofresult[, ...] andelse_result.
Example
WITHNumbersAS(SELECT90asA,2asBUNIONALLSELECT50,8UNIONALLSELECT60,6UNIONALLSELECT50,10)SELECTA,B,CASEAWHEN90THEN'red'WHEN50THEN'blue'ELSE'green'ENDASresultFROMNumbers/*------------------+ | A | B | result | +------------------+ | 90 | 2 | red | | 50 | 8 | blue | | 60 | 6 | green | | 50 | 10 | blue | +------------------*/CASE
CASEWHENconditionTHENresult[...][ELSEelse_result]ENDDescription
Evaluates the condition of each successiveWHEN clause and returns thefirst result where the condition evaluates toTRUE; any remainingWHENclauses andelse_result aren't evaluated.
If all conditions evaluate toFALSE orNULL, returns evaluation ofelse_result if present; ifelse_result isn't present, then returnsNULL.
For additional rules on how values are evaluated, see thethree-valued logic table inLogical operators.
condition must be a boolean expression. There may be multipleresult types.result andelse_result expressions must be implicitly coercible to a commonsupertype.
This expression supports specifyingcollation.
Return Data Type
Supertype ofresult[, ...] andelse_result.
Example
WITHNumbersAS(SELECT90asA,2asBUNIONALLSELECT50,6UNIONALLSELECT20,10)SELECTA,B,CASEWHENA >60THEN'red'WHENB=6THEN'blue'ELSE'green'ENDASresultFROMNumbers/*------------------+ | 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, otherwiseNULL. The remaining expressions aren't evaluated. An input expression can beany type. There may be multiple input expression types.All input expressions must be implicitly coercible to a commonsupertype.
Return Data Type
Supertype ofexpr[, ...].
Examples
SELECTCOALESCE('A','B','C')asresult/*--------+ | result | +--------+ | A | +--------*/SELECTCOALESCE(NULL,'B','C')asresult/*--------+ | result | +--------+ | B | +--------*/IF
IF(expr,true_result,else_result)Description
Ifexpr evaluates toTRUE, returnstrue_result, else returns theevaluation forelse_result.else_result isn't evaluated ifexpr evaluatestoTRUE.true_result isn't evaluated ifexpr evaluates toFALSE orNULL.
expr must be a boolean expression.true_result andelse_resultmust be coercible to a commonsupertype.
Return Data Type
Supertype oftrue_result andelse_result.
Examples
SELECT10ASA,20ASB,IF(10 <20,'true','false')ASresult/*------------------+ | A | B | result | +------------------+ | 10 | 20 | true | +------------------*/SELECT30ASA,20ASB,IF(30 <20,'true','false')ASresult/*------------------+ | A | B | result | +------------------+ | 30 | 20 | false | +------------------*/IFNULL
IFNULL(expr,null_result)Description
Ifexpr evaluates toNULL, returnsnull_result. Otherwise, returnsexpr. Ifexpr doesn't evaluate toNULL,null_result isn't evaluated.
expr andnull_result can be any type and must be implicitly coercible toa commonsupertype. Synonym forCOALESCE(expr, null_result).
Return Data Type
Supertype ofexpr ornull_result.
Examples
SELECTIFNULL(NULL,0)asresult/*--------+ | result | +--------+ | 0 | +--------*/SELECTIFNULL(10,0)asresult/*--------+ | result | +--------+ | 10 | +--------*/NULLIF
NULLIF(expr,expr_to_match)Description
ReturnsNULL ifexpr = expr_to_match evaluates toTRUE, otherwisereturnsexpr.
expr andexpr_to_match must be implicitly coercible to acommonsupertype, and must be comparable.
This expression supports specifyingcollation.
Return Data Type
Supertype ofexpr andexpr_to_match.
Example
SELECTNULLIF(0,0)asresult/*--------+ | result | +--------+ | NULL | +--------*/SELECTNULLIF(10,0)asresult/*--------+ | result | +--------+ | 10 | +--------*/Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.
Last updated 2025-12-15 UTC.