Why doesn't NOT IN always retrieve the same results as MINUS in Oracle?

Question

I have two tables:

CREATE TABLE test1
(id int);

CREATE TABLE test2
(id int);

INSERT INTO test1
VALUES (1);

INSERT INTO test1
VALUES (2);

INSERT INTO test2
VALUES (1);

Then I want to see a list of all the ids that are in test1 and not in test2.

There's at least three ways I can think of to do this:

OUTER JOIN:

SELECT a.id
FROM test1 a LEFT OUTER JOIN test2 b 
ON a.id = b.id
WHERE b.id IS NULL;

MINUS:

SELECT id
FROM test1
MINUS
SELECT id
FROM test2;

NOT IN:

SELECT id
FROM test1
WHERE id NOT IN (
                 SELECT id 
                 FROM test2
                );

So far, so good. All three of these queries should give me the same results: 1 row, with the value 2.

If I insert a null into test2, then the OUTER JOIN and MINUS queries continue to return the same results, but the NOT IN brings back no rows.

This greatly confused me. I then noticed that if I changed it to

SELECT id
FROM test1
WHERE id NOT IN (
                 SELECT id 
                 FROM test2
                 WHERE id IS NOT NULL
                );

that I get the results I was expecting - one row again.

Why does this occur? I assume this is something quite fundamental to SQL, but I'm unclear what it is (and I'm pretty sure that in other databases I've used previously, the three methods I've listed have given equivalent results - although I don't have SQL Server or postgres to test against right now, so I may be misremembering their behaviour).

(I suppose one answer to this is "Stop worrying, and just don't use NOT IN" but that can be expensive in terms of code readability - sometimes that's more elegant than doing everything with outer joins or minus.)

Answer 1

If NULL is in the NOT IN condition, then the result will always be NULL.

id NOT IN (a, b, c)

is same as:

id != a AND id != b AND id != c

Everything compare with NULL with !=, the result will be NULL, so you got empty result.

---

id IN (a, b, c)

is same as

id = a OR id = b OR id = c

, right?

So !(id = a OR id = b OR id = c) is id != a AND id != b AND id != c.