coalesce

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

coalesce: evaluates the listed expressions and returns the first non-null value. If all expressions are null, coalesce returns 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”.

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 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.
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.