COALESCE
The COALESCE function returns first value in one or more expressions that is not NULL; otherwise, it returns NULL.
Syntax
COALESCE (expression1, expression2, ...)
Remarks
COALESCE (expression1, expression2, expression3) is equivalent to this CASE statement:
CASE WHEN expression1 IS NOT NULL THEN expression1
WHEN expression2 IS NOT NULL THEN expression2
WHEN expression3 IS NOT NULL THEN expression3
ELSE NULL END
The following table lists the data types of the input arguments for COALESCE, and the resulting output type.
Data Type of expression | Output Type |
BINARY, DATE, DECIMAL, FLOAT, INTEGER, INTERVAL_DAY, INTERVAL_YEAR, NULL, STRING, TIME, TIMESTAMP, XML | Follows the ANSI SQL rules for data type precedence. |
Example
SELECT ProductID, COALESCE (UnitPrice, SalePrice, MinPrice) “Best Price”
FROM /shared/examples/ds_orders/products products