The BigQuery IF function allows you to evaluate a boolean expression and return different results based on the outcome.
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
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.
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
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.
This error occurs when the expr does not evaluate to TRUE or FALSE (ie expr is not a boolean expression).