Skip to main content

COALESCE (SQL)

A function that returns the value of the first expression that is not NULL.

Synopsis

COALESCE(expression,expression [,...])

Description

The COALESCE function evaluates a list of expressions in left-to-right order and returns the value of the first non-NULL expression. If all expressions evaluate to NULL, NULL is returned.

A string is returned unchanged; leading and trailing blanks are retained. A number is returned in canonical form, with leading and trailing zeros removed.

For further details on NULL handling, refer to NULL and the Empty String.

Data Type of Returned Value

Non-numeric expressions (such as strings or dates) must all be of the same data type, and return a value of that data type. Specifying expressions with incompatible data types results in an SQLCODE -378 error with a Datatype mismatch error message. You can use the CAST function to convert an expression to a compatible data type.

Numeric expressions may be of different data types. If you specify numeric expressions with different data types, the data type returned is the expression data type most compatible with all of the possible result values, the data type with the highest data type precedence.

A literal value (string, number, or NULL) is treated as data type VARCHAR. If you specify only two expressions, a literal value is compatible with a numeric expression: if the first expression is the numeric expression, its data type is returned; if the first expression is a literal value, the VARCHAR data type is returned.

Arguments

expression

A series of expressions to be evaluated. Multiple expressions are specified as a comma-separated list. This expression list has a limit of 140 expressions.

NULL Handling Functions Compared

The following table shows the various SQL comparison functions. Each function returns one value if the logical comparison tests True (A same as B) and another value if the logical comparison tests False (A not same as B). These functions allow you to perform NULL logical comparisons. You cannot specify NULL in an actual equality (or non-equality) condition comparison.

SQL Function Comparison Test Return Value
COALESCE(ex1,ex2,...) ex = NULL for each argument

True tests next ex argument. If all ex arguments are True (NULL), returns NULL.

False returns ex

IFNULL(ex1,ex2) [two-argument form] ex1 = NULL

True returns ex2

False returns NULL

IFNULL(ex1,ex2) [three-argument form] ex1 = NULL

True returns ex2

False returns ex3

{fn IFNULL(ex1,ex2)} ex1 = NULL

True returns ex2

False returns ex1

ISNULL(ex1,ex2) ex1 = NULL

True returns ex2

False returns ex1

NVL(ex1,ex2) ex1 = NULL

True returns ex2

False returns ex1

NULLIF(ex1,ex2) ex1 = ex2

True returns NULL

False returns ex1

Examples

The following example takes a series of values and returns the first (value d) that is not NULL. Note that the ObjectScript empty string ("") is translated as NULL in InterSystems SQL:

SELECT COALESCE("","","","firstdata","","nextdata")

The following example compares the values of two columns in left-to-right order and returns the value of the first non-NULL column. The FavoriteColors column is NULL for some rows; the Home_State column is never NULL. For COALESCE to compare the two, FavoriteColors must be cast as a string:

SELECT TOP 25 Name,FavoriteColors,Home_State,
COALESCE(CAST(FavoriteColors AS VARCHAR),Home_State) AS CoalesceCol
FROM Sample.Person

The following Dynamic SQL example compares COALESCE to the other NULL-processing functions:

  SET myquery = "SELECT TOP 50 %ID,"_
                "IFNULL(FavoriteColors,'blank') AS Ifn2Col,"_
                "IFNULL(FavoriteColors,'blank','value') AS Ifn3Col,"_
                "COALESCE(CAST(FavoriteColors AS VARCHAR),Home_State) AS CoalesceCol,"_
                "ISNULL(FavoriteColors,'blank') AS IsnullCol,"_
                "NULLIF(FavoriteColors,$LISTBUILD('Orange')) AS NullifCol,"_
                "NVL(FavoriteColors,'blank') AS NvlCol"_
                " FROM Sample.Person"
  SET tStatement = ##class(%SQL.Statement).%New()
  SET qStatus = tStatement.%Prepare(myquery)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
  SET rset = tStatement.%Execute()
  DO rset.%Display()
  WRITE !,"End of data"

See Also

FeedbackOpens in a new tab