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

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

    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
    fruitapple_or_pear
    appleIt is an apple
    bananaIt is a banana
    pearIt is a pear

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

    Related Pages

  • CASE
  • Subscribe to newsletter

    Subscribe to receive the latest blog posts to your inbox every week.

    By subscribing you agree to our Privacy Policy.
    Thank you! Your submission has been received!
    Oops! Something went wrong while submitting the form.

    Start solving your organization's biggest problems with Count today.

    Stay up to date with all things count, data and problem solving.