COALESCE vs IFNULL vs NULLIF in BigQuery

What are they and when to use them?

- IFNULL tests a column for the NULL value, returning the original value if it is NOT NULL and the second value we provide otherwise. The two columns need to be coercible to the same datatype. It's works like ISNULL in SQL Server.

- COALESCE works like IFNULL, but for multiple values. The first of them to return a non-null value is returned, otherwise resulting in a NULL.

- NULLIF allows you to replace a given value with a NULL, essentially saying "treat this value as it is was a missing value". An empty string, for example.

See below a representative example.

WITH input_data AS (

  SELECT
    CAST(NULL AS STRING) AS value_a,
    CAST(NULL AS STRING) AS value_b,
    'default value' AS default_value,
    '' AS maybe_empty_string
)

SELECT

  IFNULL(value_a, default_value) AS ifnull_example, --if a IS NULL then another value
  COALESCE(value_a, value_b, default_value) AS coalesce_example, --first non-null value among multiple options (if any)
  NULLIF(maybe_empty_string, '') AS nullif_example, -- replaces a value with a NULL

FROM input_data

---

Enjoyed this? Here are some related articles you might find useful:

• A couple of fun things about NULL in SQL
• Not all NULLS are the same
• Null-safe comparison: IS DISTINCT/NOT DISTINCT FROM
• Controlling ordering of NULL values in the ORDER BY clause