SELECT knowledge FROM sql_resources WHERE category='bigquery-standard-sql' AND slug='if'

IF

Definition

The BigQuery IF function allows you to evaluate a boolean expression and return different results based on the outcome.

expr must be a boolean expression. If expr is true then the function returns true_result, otherwise else_result is returned.

Practical Info

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.

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.

IF(expr, true_result, else_result)

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', '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

Loading code...

fruit	apple_or_pear
apple	It is an apple
banana	It is a banana
pear	It is a pear

Troubleshooting Common Errors

Could not cast literal to type DATE

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

Loading code...

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

IF