LearnNowOnline

Blog

T-SQL: COALESCE Function

Excerpt by Don Kiely | April 02, 2013

Working with null values is a common task when you allow columns to contain nulls. For example, by default, nulls in aggregates are ignored, but this may not always be the behavior you want.

When you test for nulls, always use IS NULL or IS NOT NULL. Attempts to test for nulls by using the equality operator (=) may or may not work, depending on your database settings for ANSI NULLS. When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields NULL, which amounts to an unknown value. This occurs because an unknown value cannot be compared logically against any other value. This situation occurs either when an expression is compared to the literal NULL, or when two expressions are compared and one of them evaluates to NULL.

COALESCE Function

The COALESCE function returns the first non-null expression in a series of expressions. The logic goes something like this: If expression1 is not null, then return expression1. If expression1 is null and expression2 is not null, then return expression2, and so on:

COALESCE(expression1, expression2 [,...n])

Use COALESCE when you want to return the first expression among its arguments that is not null. In the following example, COALESCE returns 10, the third value in the list and the first that does not have a NULL value. If all of the arguments for COALESCE return null, then the statement returns null.

SELECT COALESCE(3+NULL, 2*NULL, 5*2, 7);

Here's an example that uses COALESCE to format a location value by using Region if it isn't null and otherwise using Country. The Figure below shows some of the results.

SELECT CompanyName, Region, Country,

City + ', ' + COALESCE(Region, Country) AS Location

FROM dbo.Suppliers;