COALESCE

Definition

The COALESCE function in BigQuery will return the first non-NULL expression. It's often used to safely handle NULL values.

Returns: Supertype of expr

Practical Info

• Often used similarly to IFNULL

Common Questions

How do I include NULL values in my aggregations?

In this case if you want to include NULL values in your aggregations, like AVG, you can use COALESCE to convert any nulls to a number. In this column, we convert all NULL values to 1 before taking the SUM so the NULL row is included in customers but not in normal_sum.

In this case the result of the AVG of the same column is different whether the NULL row is included as a 0 or not included at all.

How to JOIN 2 tables with COALESCE?

Let's say you want to join 2 tables, and if you can you want to join on user_id, but if the user_id is null you want to join on the name. You can use COALESCE to help with that!

Troubleshooting Common Errors

No matching signature for function COALESCE for argument types. Supported signature: COALESCE([ANY, ...])

This error happens when BigQuery encounters different data types. Since it needs to create a column of a single type, it will error if it sometimes evaluates to a STRING and sometimes an INT64.

To resolve this use CAST to ensure your COALESCE parameters are all of the correct type.

COALESCE(expr[, ...])

Related Pages

• Data Types in Standard SQL

SELECT
  COALESCE(NULL, 'A', 'B') AS coalesced

SELECT
  AVG(COALESCE(is_customer, 0)) AS customers,
  AVG(is_customer) AS normal_sum
FROM
  (
    SELECT
      1 AS is_customer
    UNION ALL
(    SELECT
      NULL AS is_customer)
    UNION ALL
(    SELECT
      0 AS is_customer)
  ) AS table_3

with table1 as (
  SELECT
  1 AS user_id,
  'Dave' AS name
UNION ALL
(SELECT
  NULL AS user_id,
  'Nancy' AS name)
UNION ALL
(SELECT
  2 AS user_id,
  'Bob' AS name)
),
table2 as (SELECT
  1 AS user_id,
  'Dave' AS name,
  87 AS score
UNION ALL
(SELECT
  NULL AS user_id,
  'Nancy' AS name,
  92 AS score))
SELECT
  *
FROM
  table1
  FULL OUTER JOIN table2 ON (COALESCE(CAST(table1.user_id AS STRING), table1.name) = COALESCE(CAST(table2.user_id AS STRING), table2.name))

SELECT
  *
FROM
  table1
FULL OUTER JOIN table2 ON (COALESCE(CAST(table1.user_id AS STRING), table1.name) = COALESCE(CAST(table2.user_id AS STRING), table2.name))