COALESCE (T-SQL)

Returns the first nonnull expression among its arguments.

Syntax

COALESCE(expression [,...n])

Arguments

expression

Is an expression of any type.

n

Is a placeholder indicating that multiple expressions can be specified. All expressions must be of the same type or must be implicitly convertible to the same type.

Return Types

Returns the same value as expression.

Remarks

If all arguments are NULL, COALESCE returns NULL.

COALESCE(expression1,...n) is equivalent to this CASE function:

CASE
        WHEN (expression1 IS NOT NULL) THEN expression1
        ...
        WHEN (expressionN IS NOT NULL) THEN expressionN
        ELSE NULL

Examples

In this example, the wages table is shown to include three columns with information about an employee’s yearly wage: hourly_wage, salary, and commission. However, an employee receives only one type of pay. To determine the total amount paid to all employees, use the COALESCE function to receive only the nonnull value found in hourly_wage, salary, and commission.

SET NOCOUNT ON

GO

USE master

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES

        WHERE TABLE_NAME = 'wages')

    DROP TABLE wages

GO

CREATE TABLE wages

(

    emp_id        tinyint     identity,

    hourly_wage    decimal    NULL,

    salary        decimal     NULL,

    commission    decimal    NULL,

    num_sales    tinyint    NULL

)

GO

INSERT wages VALUES(10.00, NULL, NULL, NULL)

INSERT wages VALUES(20.00, NULL, NULL, NULL)

INSERT wages VALUES(30.00, NULL, NULL, NULL)

INSERT wages VALUES(40.00, NULL, NULL, NULL)

INSERT wages VALUES(NULL, 10000.00, NULL, NULL)

INSERT wages VALUES(NULL, 20000.00, NULL, NULL)

INSERT wages VALUES(NULL, 30000.00, NULL, NULL)

INSERT wages VALUES(NULL, 40000.00, NULL, NULL)

INSERT wages VALUES(NULL, NULL, 15000, 3)

INSERT wages VALUES(NULL, NULL, 25000, 2)

INSERT wages VALUES(NULL, NULL, 20000, 6)

INSERT wages VALUES(NULL, NULL, 14000, 4)

GO

SET NOCOUNT OFF

GO

SELECT CAST(COALESCE(hourly_wage * 40 * 52,

    salary,

    commission * num_sales) AS money) AS 'Total Salary'

FROM wages

GO

Here is the result set:

Total Salary

------------

20800.0000

41600.0000

62400.0000

83200.0000

10000.0000

20000.0000

30000.0000

40000.0000

45000.0000

50000.0000

120000.0000

56000.0000

(12 row(s) affected)

See Also