deepak.a (10/27/2010)
To get the records with NULL value the WHERE condition needs to rewrite as WHERE NullableColumn <> '1' OR NullableColumn IS NULL.For the above you can write like this also
SET ANSI_NULLS OFF
GO
SELECT NullableColumn FROM NullOperation WHERE NullableColumn IN('1' ,NULL)
GO
it will depends on SET OPTION
Reference http://msdn.microsoft.com/en-us/library/ms188048(SQL.90).aspx"> http://msdn.microsoft.com/en-us/library/ms188048(SQL.90).aspx
This is not equivalent; the original query excludes the value '1', whereas the IN clause includes '1' and NULL and excludes all others.
If you use SET ANSI_NULLS OFF, there is no need to rewrite anyway. The ANSI_NULLS affects all comparisons with NULL, not only those in a [NOT] IN expression.
And beware that the ANSI_NULLS OFF option is deprecated since SQL Server 2008. This option will be removed in a future version; don't build any new code that relies on this setting. (Though you should never have in the first placve, given how extremely non-standard this option is).