0

Take these two simple statements:

SELECT '1 IS NOT IN THE LIST' WHERE 1 NOT IN (2, 3, 4)
SELECT '1 IS NOT IN THE LIST' WHERE 1 NOT IN (NULL, 2, 3, 4)

The results you will get are:

enter image description here

Basically, as soon as you add NULL to the list, the NOT IN condition always returns FALSE, implying that (in this case) 1 = NULL

Can someone explain why this happens?

Following on from that, why does this statement (the opposite of the NOT IN that returned FALSE) not return TRUE?

SELECT '1 IS IN THE LIST' WHERE 1 IN (NULL, 2, 3, 4)
Jimbo
  • 22,379
  • 42
  • 117
  • 159
  • 1
    Somebody already give us an answer. [https://stackoverflow.com/questions/129077/not-in-clause-and-null-values](https://stackoverflow.com/questions/129077/not-in-clause-and-null-values) regards, Arnaud – Arnaud Gastelblum Feb 28 '18 at 08:08
  • well covered question. if you want this behaviour run: set ansi_nulls OFF – Nick Kavadias Feb 28 '18 at 08:15
  • 1
    @NickKavadias - terrible advice to be giving in 2018 when Microsoft have been advising for years that they plan to force ansi_nulls ON in a future version of the product and you [should](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-ansi-nulls-transact-sql) "Avoid using this feature in new development work, and plan to modify applications that currently use this feature" – Damien_The_Unbeliever Feb 28 '18 at 08:17

0 Answers0