coalesce

Description

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

Syntax

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

Parameters

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

Examples

Example 1

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

Example 2

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

Usage

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute coalesce.

See also

Commands case, nullif, select, if...else, where clause