The SQL Procedure

COALESCE Function

Returns the first nonmissing value from a list of columns.

Featured in:

Performing an Outer Join

COALESCE (column-name <, ... column-name>)

Arguments

column-name: is described in column-name.

Details

COALESCE accepts one or more column names of the same data type. The COALESCE function checks the value of each column in the order in which they are listed and returns the first nonmissing value. If only one column is listed, the COALESCE function returns the value of that column. If all the values of all arguments are missing, the COALESCE function returns a missing value.

In some SQL DBMSs, the COALESCE function is called the IFNULL function. See PROC SQL and the ANSI Standard for more information.

Note: If your query contains a large number of COALESCE function calls, it might be more efficient to use a natural join instead. See Natural Joins. [cautionend]

Top of Page