COALESCE function. Definition, syntax, examples and common errors using BigQuery Standard SQL. The COALESCE function will return the first non-NULL expression.
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
| coalesced |
|---|
| A |
IFNULLIn 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.
| customers | normal_sum |
|---|---|
| 0.33333333333333337 | 0.5 |
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.
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!
| user_id | name |
|---|---|
| 1 | Dave |
| null | Nancy |
| 2 | Bob |
| user_id | name | score |
|---|---|---|
| 1 | Dave | 87 |
| null | Nancy | 92 |
| user_id | name | user_id_1 | name_1 | score |
|---|---|---|---|---|
| 1 | Dave | 1 | Dave | 87 |
| null | Nancy | null | Nancy | 92 |
| 2 | Bob | null | null | null |
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.
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[, ...])SELECT
COALESCE(NULL, 'A', 'B') AS coalescedSELECT
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_3with 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))