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 exception integers with doubles (in this case, integers are promoted to doubles).
COALESCE does not support multi-assign 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 ...