NOT IN and NULL values

[Former Member]

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

[Former Member · Accepted solution]

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)

[VolkerBarth · Accepted solution]

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