T-SQL Tutorial

COALESCE


In SQL Server, the COALESCE expression is used to handle NULL values. If the expression is null, then returns a default value. During the evaluation of the expression, the user-defined values take the place of the NULL values. A non-null result is always returned from an argument list that has been evaluated using SQL COALESCE expression.

In order to prevent having your results ruined by null values, it's imperative that you learn how to deal with missing values in your datasets. You should know how they affect computations, queries, reports, and data-set planning.


Properties of the SQL Coalesce

The expressions must all use the same data types.
It can have more than one expression.
Using SQL's Coalesce expression is a shortcut for using the Case expression.
When an integer and a character expression are used together, the result is always an integer.


COALESCE Syntax:

COALESCE ( expression, ...n_value )


COALESCE Example:

SELECT COALESCE(null,11) as Value;

Value
11

Using COALESCE with character string data

In the following example, the string "Hi" is returned because it is the first non-null argument.
SELECT COALESCE(NULL, 'Test 1', 'Test 2', NULL);
SELECT COALESCE(NULL, NULL, 'Test 2', NULL);

See also: T-SQL Functions -> HOST_ID | HOST_NAME | ISNULL | ISNUMERIC | NULLIF