IF
Definition
The BigQuery IF
function allows you to evaluate a boolean expression and return different results based on the outcome.
Syntax
IF(expr, true_result, else_result)
expr
must be a boolean expression. If expr
is true then the function returns true_result
, otherwise else_result
is returned.
SELECT
fruit,
IF((fruit = 'apple'), 'It is an apple', 'It is a pear') AS apple_or_pear
FROM
(
SELECT
'apple' AS fruit
UNION ALL
( SELECT
'apple' AS fruit)
UNION ALL
( SELECT
'pear' AS fruit)
) AS table_3
Practical Info
expr
is NULL
then the else_result is returned.true_result
and else_result
can be any data type but must be of the same type.IF
function is similar to the CASE
function.Common Questions
Which is better - IF
or CASE
expressions?
For almost all situations you can use IF
or CASE
interchangeably, but their syntax makes them better suited to different situations. For simple comparisons IF
functions are easier to write, but when there is a list of outcomes the structure of the CASE
syntax makes it easier to review.
How can I model situations with more than two possible outcomes?
It is possible to "chain" IF
functions together to provide results for a number of different logical outcomes by replacing true_result
and else_result
with a followup IF
function. For example:
SELECT
fruit,
IF((fruit = 'apple'), 'It is an apple', IF((FRUIT.fruit = 'banana'), 'It is a banana', 'It is a pear')) AS apple_or_pear
FROM
(
SELECT
'apple' AS fruit
UNION ALL
( SELECT
'banana')
UNION ALL
( SELECT
'pear')
) AS FRUIT
Troubleshooting Common Errors
Could not cast literal to type DATE
This error occurs when the two different results true_result
and else_result
are of different data types, specifically in this example one of the results is a date and the other is a data type which cannot be converted to a date. If you get this, check your logic and column types or consider using the CAST
function.
No matching signature for function IF for argument types... Supported signature: IF(BOOL, ANY, ANY).
This error occurs when the expr
does not evaluate to TRUE
or FALSE
(ie expr
is not a boolean expression).