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
| coalesced |
|---|
| A |
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.
| 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.
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!
| 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.
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