COALESCE Expression

Returns the first non-null value from a list of columns.

Restriction:

SAS data sets and SPD Engine data sets process null values as a blank string.

Syntax

COALESCE(expression [, …expression])

Arguments

expression

specifies any valid SQL expression.

See	<sql-expression>
	FedSQL Expressions

Details

COALESCE accepts one or more SQL expressions of the same data type. The COALESCE expression checks the value of each SQL expression in the order in which it is e listed and returns the first non-null value. If only one SQL expression is listed, the COALESCE expression returns the value of that SQL expression. If all the values of all arguments are null, the COALESCE expression returns a null value.

In some SQL DBMSs, the COALESCE expression is called the IFNULL expression.

Note: If your query contains a large number of COALESCE expressions, it might be more efficient to use a natural join instead. For more information, see Natural Joins.

Comparisons

The COALESCE expression is a variation of the CASE expression. For example, these two sets of code are equivalent,

coalesce(value1, value2, value3)

case
   when value1 is not null
      then value1
   when value2 is not null
      then value2
   else value3
end;

COALESCE Expression

Syntax

Arguments

expression

Details

Comparisons

See Also