COALESCE

Definition

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

Syntax

COALESCE(expr[, ...])

Returns: Supertype of expr

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

Practical Info

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.

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

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!

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

Note - the join columns have to be the same type. In this case we casted our user_id to a string to make it compatible with the name column.

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.

Related Pages