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
SELECT COALESCE(NULL, 'A', 'B') AS coalesced
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.
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))
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.