Yesterday I worked on a plane vanilla query that I couldn't get to return the expected result set:
I finally found out that problem was that the NOT IN query returned NULL values.
--This one returns 1 row SELECT 1 as Test WHERE Test not in (2,3,4) --While this one returns 0 rows SELECT 1 as Test WHERE Test not in (2,3,4,null)
I find this very illogical, and I could not find it documented anywhere. Is this a bug or a feature? :-)
I can't believe that I haven't stumbled across this problem before. And I fear that I have, but just not noticed...
I'm afraid this is a feature of SQL's three-valued-logic.
The following query shows that both "1 in (2,null)" and "1 not in (2,null)" evaluate to SQL's UNKNOWN truth value.
SELECT 1 as Test , case when Test in (2,NULL) is true then 'T' when Test in (2,NULL) is false then 'F' when Test in (2,NULL) is unknown then 'U' end case as [in] , case when Test not in (2,NULL) is true then 'T' when Test not in (2,NULL) is false then 'F' when Test not in (2,NULL) is unknown then 'U' end case as [not in]
The IN predicate "Test in (2,NULL)" is interpreted "Test=2 OR Test=NULL". Without the NOT, this doesn't give very surprising results because UNKNOWN is interpreted as FALSE in the WHERE clause as you used it. This does become surprising when adding NOT because "NOT (UNKNOWN)" is UNKNOWN.
To avoid these surprises, you could either avoid NULL values in your list or use the "IS NOT TRUE" instead of "NOT":
SELECT 1 as Test WHERE ( Test in (2,3,4,NULL) is not true)
answered 16 Sep '11, 03:42
Ivan T. Bowman