SQL Server COALESCE() Explained

Posted on by Ian

In SQL Server, the COALESCE() expression returns its first non-null argument.

The way it works is, we pass a list of arguments to the expression, it evaluates the arguments in order and returns the current value of the first expression that initially doesn’t evaluate to NULL.

Syntax

The syntax goes like this:

COALESCE ( expression [ ,...n ] )

Example

Here’s a simple example to demonstrate:

SELECT COALESCE(null, 'Cat', 'Dog');

Result:

Cat

In this case, Cat was the first non-NULL argument, and so COALESCE() returned that value.

Expressions

As mentioned, COALESCE() returns the current value of the first expression that initially doesn’t evaluate to NULL. Therefore, if we pass an expression like this:

SELECT COALESCE( null, 2 * 3 );

We get this:

6

The function returns the data type of the expression with the highest data type precedence. If all expressions are nonnullable, the result is typed as nonnullable.

When All Arguments are NULL

If all arguments are NULL, COALESCE() returns NULL. However, at least one of the null values must be a typed NULL, otherwise an error occurs.

In other words, they can’t all be the NULL constant:

SELECT COALESCE( null, null );

We get this:

Msg 4127, Level 16, State 1, Line 1
At least one of the arguments to COALESCE must be an expression that is not the NULL constant.

In this case, all arguments were the NULL constant, and so an error was returned.

Below is a database example to demonstrate a scenario where COALESCE() returns NULL when all arguments are NULL.

Suppose we run the following query:

SELECT CustomerId, CustomerCategoryId
FROM Sales.SpecialDeals
WHERE SpecialDealId = 1;

Result:

CustomerId  CustomerCategoryId
----------- ------------------
NULL        NULL

Both columns contain NULL values.

So if we pass both columns to COALESCE(), we get a result of NULL:

SELECT COALESCE( CustomerId, CustomerCategoryId )
FROM Sales.SpecialDeals
WHERE SpecialDealId = 1;

Result:

NULL

The same is true if we replace one of the columns with the NULL constant:

SELECT COALESCE( CustomerId, null )
FROM Sales.SpecialDeals
WHERE SpecialDealId = 1;

Result:

NULL

So it’s only when all arguments are the NULL constant that we get an error.

Replace NULL Results with a Known Value

We can include a known value as the last argument to replace any NULL results with that known value.

For example, the following query returns NULL:

SELECT SUM( UnitPrice ) 
FROM Sales.SpecialDeals;

Result:

NULL

In this case, the UnitPrice column contains NULL values in all rows, and so the result was NULL.

We can use COALESCE() like this:

SELECT COALESCE( SUM( UnitPrice ), 0 )
FROM Sales.SpecialDeals;

Result:

0.00

Now, the NULL result is replaced with a known value (zero).

COALESCE() vs CASE

The COALESCE() expression is actually a syntactic shortcut for the CASE expression. When we use the COALESCE() expression, the query optimizer rewrites it as a CASE expression.

When I run the following statement:

SELECT COALESCE( CustomerId, CustomerCategoryId )
FROM Sales.SpecialDeals
WHERE SpecialDealId = 1;

The query optimizer rewrites it to the following:

CASE 
WHEN [WideWorldImporters].[Sales].[SpecialDeals].[CustomerID] IS NOT NULL THEN [WideWorldImporters].[Sales].[SpecialDeals].[CustomerID] 
ELSE [WideWorldImporters].[Sales].[SpecialDeals].[CustomerCategoryID] 
END

COALESCE() vs ISNULL()

In some ways, the COALESCE() expression is similar the ISNULL() function. But there are differences. In particular:

  • ISNULL() is a function and is evaluated only once. COALESCE() on the other hand, is an expression, and can potentially be evaluated multiple times.
  • Data type determination of the resulting expression is different. ISNULL uses the data type of the first parameter, COALESCE follows the CASE expression rules and returns the data type of value with the highest precedence.
  • The NULLability of the result expression is different for ISNULL and COALESCE. The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one). By contrast,COALESCE with non-null parameters is considered to be NULL.
  • Validations for ISNULL and COALESCE are also different. For example, a NULL value for ISNULL is converted to int though for COALESCE, you must provide a data type.
  • ISNULL takes only two parameters. By contrast COALESCE takes a variable number of parameters.

More Information

See Microsoft’s documentation for more details and more complex examples.