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