on 2011 Sep 16 4:24 AM
Yesterday I worked on a plane vanilla query that I couldn't get to return the expected result set:
SELECT ...
FROM ...
WHERE Text NOT IN (SELECT otherText FROM anotherTable)
No matter what, it did not return any rows:-(
I finally found out that problem was that the NOT IN query returned NULL values.
I can illustrate the problem with this simple queries:
--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...
Ove B-)
Request clarification before answering.
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)
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I would add that this may be surprising but does not seem illogical:
If you have a set of elements, and at least one of them is unknown, you simply can't tell whether the one element you're looking for is in the set or not, if it's not among the known elements.
That's "three-valued-logical", methinks.
(I surely won't claim that I've never stumbled over this or noticed every case of wrong usage. Well, I'd better claim the opposite is true.)
User | Count |
---|---|
76 | |
30 | |
8 | |
8 | |
7 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.