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

Hugo Kornelis (10/28/2010)

---

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

Hugo Kornelis (10/28/2010)

---

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.

SET ANSI_NULLS OFF;
GO
SELECT NullableColumn FROM NullOperation WHERE NullableColumn <> '1';
GO

NullableColumn
--------------
0

(1 row(s) affected)

SET ANSI_NULLS ON;
GO
SELECT NullableColumn FROM NullOperation WHERE NullableColumn <> NULL;
GO

NullableColumn
--------------

(0 row(s) affected)

SET ANSI_NULLS OFF;
GO
SELECT NullableColumn FROM NullOperation WHERE NullableColumn <> NULL;
GO

NullableColumn
--------------
0
1

(2 row(s) affected)

Hugo Kornelis (10/28/2010)

---

I must say that I am also very surprised (and disappointed) by the amount of incorrect answers. Almost 25% of respondents expect NULL to be returned as well - much more that I expected, because this is far from the first time that the effects of NULL in comparisons have been tested in the QotD.

vk-kirov (10/28/2010)

---

Setting this option wouldn't be enough, because it doesn't affect the comparison of a nullable column with a not-null value.