Yesterday I worked on a plane vanilla query that I couldn't get to return the expected result set:
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-)

asked 16 Sep '11, 03:24

Ove%20Halseth's gravatar image

Ove Halseth
accept rate: 0%

edited 15 Mar '13, 22:06

Mark%20Culp's gravatar image

Mark Culp

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

answered 16 Sep '11, 03:42

Ivan%20T.%20Bowman's gravatar image

Ivan T. Bowman
accept rate: 39%

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

(16 Sep '11, 07:47) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 16 Sep '11, 03:24

question was seen: 2,568 times

last updated: 15 Mar '13, 22:06