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.
Refactoring Self 