coalesce

Supports conditional SQL expressions; can be used anywhere a value expression can be used; alternative for a case expression.

coalesce(expression, expression [, expression]...)

coalesce: evaluates the listed expressions and returns the first non-null value. If all the expressions are null, coalesce returns a null.

expression: 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” on page 249 in Chapter 4, “Expressions, Identifiers, and Wildcard Characters” of Reference Manual: Building Blocks.

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

This is an alternative way of writing Example 1:

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.
coalesce expressions can be used anywhere an expression can be used 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 “Datatype of mixed-mode expressions” on page 6 in Chapter 1, “System and User-Defined Datatypes” of Reference Manual: Building Blocks. 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 caseexpression. 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.

coalesce permission defaults to all users. No permission is required to use it.