MyNullableCol <> 'MyValue' Doesn't Includes Rows where MyNullableCol IS NULL

Question

Dear Gurus

Today I found a strange problem that is I have a Table With a Nullable Column and I tried to use following Query

SELECT * Id, MyNCol, FROM dbo.[MyTable] WHERE MyNCol <> 'MyValue'

And Expecting it to return all rows not having 'MyValue' value in Field MyNCol. But its not returning all those Rows Containing NULL in above specified Column(MyNCol). So I have to Rewrite my query to

SELECT * Id, MyNCol, FROM dbo.[MyTable] WHERE MyNCol <> 'MyValue' OR MyNCol IS NULL

Now My question is why is it why the first query is not sufficient to perform what i desire. :(

Regards Mubashar

Answer 1

Equality operators cannot be performed on null values. In SQL 2000, you could check null = null, however in 2005 this changed.

Answer 2

Look into the behaviour of "ANSI NULLs".

Standardly, NULL != NULL. You can change this behaviour by altering the ANSI NULLS enabled property on your database, but this has far ranging effects on column storage and query execution. Alternatively, you can use the SET ANSI_NULLS statement in your SQL script.

MSDN Documentation for SQL Server 2008 is here:
SET ANSI_NULLS (Transact-SQL)

Pay particular attention to the details of how comparisons are performed - even when setting ANSI_NULLS OFF, column to column comparisons may not behave as you intended. It is best practice to always use IS NULL and IS NOT NULL.

Answer 3

NULL = UNKNOWN. So you can neither say that MyNCol = 'My Value' nor that MyNCol <> 'My Value' - both evaluate to UNKNOWN (just like 'foo' + MyNCol will still become NULL). An alternative to the workaround you posted is:

WHERE COALESCE(MyNCol, 'Not my Value') <> 'My Value';

This will include the NULL columns because the comparison becomes false rather than unknown. I don't like this option any better because it relies on some magic value in the comparison, even though that magic value will only be supplied when the column is NULL.