So, I was seeing some weirdness with NOT IN, and reduced it to this minimal case:
SELECT 'foo'
WHERE 1 NOT IN (NULL, 2)
----
(0 row(s) affected)
Should that simply see that 1 is not in the set (NULL, 2) and successfully select 'foo'? Ok... maybe it thinks 1 is actually in that
set.
SELECT 'foo'
WHERE 1 IN (NULL, 2)
----
(0 row(s) affected)
Hrm, guess not. Ok, so 1 is certainly not in the set of (NULL, 2). Maybe its something weird with NOT IN.
SELECT 'foo'
WHERE NOT(1 IN (NULL, 2))
----
(0 row(s) affected)
Ok... so 1 is not in set, but 1 is also
not not in the set. So maybe there's something weird with IN and NULLs.
SELECT 'foo'
WHERE 1 IN (NULL, 2, 1)
----
foo
(1 row(s) affected)
No, IN works just fine when 1 is in the set. So maybe NOT IN is just broken?
SELECT 'foo'
WHERE 1 NOT IN (2, 3)
----
foo
(1 row(s) affected)
No, NOT IN works just fine, as long as you don't have any nulls. Is that the lesson here? Never use NOT IN with NULLs?
From the MSDN docs (paraphrased):
a list of expressions to test for a match. All expressions must be of the same type as the test_expression.
So I guess because NULL isn't the same type as 1, it is just failing?
SELECT 'foo'
WHERE 1 NOT IN (CAST(NULL as int), 2)
----
(0 row(s) affected)
Didn't think that'd work.
I guess this means anywhere I'm using NOT IN with a subquery, I need to be sure to wrap the selected column in an ISNULL. Can anyone
tell me I'm wrong on this? Pretty please?