Blog

T-SQL: COALESCE Function

Excerpt by Don Kiely

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; 
Using COALESCE ldn-expertdkiely This post is an excerpt from the online courseware for our SQL Server 2012: Introduction to T-SQL course written by expert Don Kiely.

 



Don Kiely
Don Kiely is a featured instructor on many of our SQL Server and Visual Studio courses. He is a nationally recognized author, instructor, and consultant specializing in Microsoft technologies. Don has many years of teaching experience, is the author or co-author of several programming books, and has spoken at many industry conferences and user groups. In addition, Don is a consultant for a variety of companies that develop distributed applications for public and private organizations.


This course excerpt was originally posted April 02, 2013 from the online courseware SQL Server 2012, Part 7 of 9: Introduction to T-SQL by Don Kiely

SQL Server