Microsoft® SQL Server® 2012 Bible

187

Chapter 8: Data Types, Expressions, and Scalar Functions

8

The IS operator may be combined with NOT to test for the presence of a value by restrict-

ing the result set to those rows where MiddleName is not null:

USE AdventureWorks

GO

SELECT FirstName, MiddleName, LastName

FROM Person.Person

WHERE

MiddleName IS NOT NULL

ORDER BY

LastName,

FirstName

Result:

FirstName MiddleName LastName

---------- ---------- ---------...

Syed E Abbas

Catherine R. Abel

Kim B Abercrombie

Hazem E Abolrous

Handling Nulls

When you supply data to reports, to end users, or to some applications, a null value is less

than welcome. Often a null must be converted to a valid value so that the data may be

understood, or so the expression won’t fail.

Nulls require special handling when used within expressions, and SQL includes a few func-

tions designed specifi cally to handle nulls. ISNULL() and COALESCE() convert nulls to

usable values, and NULLIF() creates a null if the specifi ed condition is met.

Using the COALESCE() Function

COALESCE() is not used as often as it could (some would say should) be, perhaps because

it’s not well known. It’s a cool function. COALESCE() accepts a list of expressions or col-

umns and returns the fi rst non-null value, as follows:

COALESCE(expression, expression, ...)

COALESCE() is derived from the Latin words co + alescre, which mean to unite toward a

common end, to grow together, or to bring opposing sides together for a common good.

The SQL keyword, however, is derived from the alternate meaning of the term: “to arise

from the combination of distinct elements.” In a sense, the COALESCE() function brings

together multiple, differing values of unknown usefulness, and from them emerges a single

valid value.

Functionally, COALESCE() is the same as the following case expression:

c08.indd 187c08.indd 187 7/30/2012 4:21:14 PM7/30/2012 4:21:14 PM

http://www.it-ebooks.info