我有两张桌子:
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”,但这在代码可读性方面可能很昂贵 – 有时这比使用外连接或减去所有内容更优雅.)