IF

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

• If 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.

• The IF function is similar to the CASE function.

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).

• CASE