Microsoft® SQL Server® 2012 Bible

186

Part II: Building Databases and Working with Data

IF NULL

= NULL

SELECT '=';

ELSE

SELECT '<> ';

Result:

<>

Because the = and <> operators can’t check for nulls, SQL includes two special operators, IS

and IS NOT, to test for equivalence to special values, as follows:

WHERE Expression IS NULL

Repeating the simple test, the IS search operator works as advertised:

IF NULL

IS NULL

SELECT 'Is';

ELSE

SELECT 'Is Not';

Result:

Is

The IS search condition may be used in the SELECT statement’s WHERE clause to locate

rows with null values. Most of the AdventureWorks people in the Persons table do not have

a middle name in the database. The following query retrieves only those people with a null

in the MiddleName column:

USE AdventureWorks

GO

SELECT FirstName, MiddleName, LastName

FROM Person.Person

WHERE

MiddleName IS NULL

ORDER BY

LastName,

FirstName

Result:

FirstName MiddleName LastName

----------- ----------- ------------

Kim NULL Abercrombie

Kim NULL Abercrombie

Sam NULL Abolrous

Humberto NULL Acevedo

...

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

http://www.it-ebooks.info