oracle中sql语句minus,sql – 为什么不总是在Oracle中检索与MINUS相同的结果？

我有两张桌子：

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

然后我想查看test1中所有id的列表,而不是test2中的id.

我至少有三种方法可以做到这一点：

外部加入：

SELECT a.id

FROM test1 a LEFT OUTER JOIN test2 b

ON a.id = b.id

WHERE b.id IS NULL;

减去：

SELECT id

FROM test1

MINUS

SELECT id

FROM test2;

不在：

SELECT id

FROM test1

WHERE id NOT IN (

SELECT id

FROM test2

);

到现在为止还挺好.所有这三个查询都应该给我相同的结果：1行,值为2.

如果我在test2中插入一个null,那么OUTER JOIN和MINUS查询将继续返回相同的结果,但NOT IN不会返回任何行.

这让我很困惑.然后我注意到,如果我把它改成了

SELECT id

FROM test1

WHERE id NOT IN (

SELECT id

FROM test2

WHERE id IS NOT NULL

);

我得到了我期待的结果 – 再一行.

为什么会这样？我认为这是SQL非常重要的东西,但是我不清楚它是什么(我很确定在我之前使用过的其他数据库中,我列出的三种方法都给出了相同的结果 – 尽管我不喜欢现在没有SQL Server或postgres进行测试,所以我可能会错误地记录他们的行为.

(我想对此的一个答案是“不要担心,只是不要使用NOT IN”,但这在代码可读性方面可能很昂贵 – 有时这比使用外连接或减去所有内容更优雅.)