Supports conditional SQL expressions; can be used anywhere a value expression can be used; alternative for a case expression.
coalesce(expression, expression [, expression]...)
evaluates the listed expressions and returns the first non-null value. If all expressions are null, coalesce returns NULL.
is a column name, a constant, a function, a subquery, or any combination of column names, constants, and functions connected by arithmetic or bitwise operators. For more information about expressions, see “Expressions”.
Returns the first occurrence of a non-null value in either the lowqty or highqty column of the discounts table:
select stor_id, discount, coalesce (lowqty, highqty) from discounts
An alternative way of writing the previous example:
select stor_id, discount, case when lowqty is not NULL then lowqty else highqty end from discounts
coalesce expression simplifies standard SQL expressions by allowing you to express a search condition as a simple comparison instead of using a when...then construct.
You can use coalesce expressions anywhere an expression in SQL.
At least one result of the coalesce expression must return a non-null value. This example produces the following error message:
select price, coalesce (NULL, NULL, NULL) from titles
All result expressions in a CASE expression must not be NULL.
If your query produces a variety of datatypes, the datatype of a case expression result is determined by datatype hierarchy, as described in “Datatypes of mixed-mode expressions”. If you specify two datatypes that Adaptive Server cannot implicitly convert (for example, char and int), the query fails.
coalesce is an abbreviated form of a case expression. Example 2 describes an alternative way of writing the coalesce statement.
coalesce must be followed by at least two expressions. This example produces the following error message:
select stor_id, discount, coalesce (highqty) from discounts
A single coalesce element is illegal in a COALESCE expression.
ANSI SQL – Compliance level: Transact-SQL extension.
Any user can execute coalesce.
Commands case, nullif, select, if...else, where clause