COALESCE

The COALESCE expression allows for user-specified NULL-handling. It is often used to fill in missing values in dirty data.

It has a function-like syntax, but can take unlimited arguments, for example: COALESCE(a, b, c, x, y, z).

You can use the COALESCE expression to evaluate records for multiple values and return the first non-NULL value encountered, in the order specified. The following requirements apply:

You can specify two or more arguments to COALESCE.
Arguments that you specify to COALESCE must all be of the same type, with the following exceptions:
- Integers with doubles (resulting in doubles)
- Strings with managed attributes (resulting in managed attributes)

In the following example, all records without a specified price are treated as zero in the computation:

AVG(COALESCE(price, 0))

COALESCE can also be used without aggregation, for example:

SELECT COALESCE(price, 0) AS price_or_zero WHERE ...