SQL Server Null Values ~ Into the Void (pun intended)

Null values can get some people rather confused writing queries. I hope I can shed some light on that. By default, SQL Server uses ANSI Nulls. This behavior says that any value compared to null is not equal to null.

if ‘a’=null
print ‘true’
else
print ‘false’

Returns false

Here is something interesting and if you are slightly devious, what about null compared to null?

if null=null
print ‘true’
else
print ‘false’

Returns false

if null is null
print ‘true’
else
print ‘false’

Returns true

Lets turn off ANSI_Null:

SET ANSI_NULL OFF;

if null=null
print ‘true’
else
print ‘false’ 

RETURNS TRUE!!!!!

if null is null
print ‘true’
else
print ‘false’ 

Returns true

According to MSDN, if you are trying to search for or compare null values using “is null” is recommended and will return consistent results.

If you are trying to create a query and want to include nulls in the result set you have to do it explicitly like this:
WHERE name = ‘Smith’ OR name IS NULL
Or if you want to exclude nulls:
WHEREname = ‘Smith’ OR name IS NOT NULL

If you want to be tricky you could do something like:

WHERE ISNULL (name,) = ‘Smith’

In London, they have signs that say “Mind the gap”, I say MIND THE NULLS!

One handy function to deal with nulls is Coalesce, which I describe here.

Advertisements

Have an opinion? Leave an opinion!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s