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

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

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

Usage

Standards

SQL92 – Compliance level: Transact-SQL extension.

Permissions

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

See also

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