Hugo Kornelis (10/28/2010)

---

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).