“not in”和“not exists”有什么区别？（涉及IT技术）

Question

not in 和 not exists 在 Oracle 查询中有什么区别？

何时使用 not in，何时使用 not exist？

- Gold

Answer 1

NOT IN 和 NOT EXISTS 的区别在于结果中包含 NULL 值时会更加明显。

例如：

create table test_a (col1 varchar2(30 char));
create table test_b (col1 varchar2(30 char));

insert into test_a (col1) values ('a');
insert into test_a (col1) values ('b');
insert into test_a (col1) values ('c');
insert into test_a (col1) values ('d');
insert into test_a (col1) values ('e');

insert into test_b (col1) values ('a');
insert into test_b (col1) values ('b');
insert into test_b (col1) values ('c');
insert into test_b (col1) values (null);

请注意：关键区别在于test_b 包含了一个 null 值。

select * from test_a where col1 not in (select col1 from test_b);

没有返回任何行

select * from test_a where 
    not exists
        (select 1 from test_b where test_b.col1 = test_a.col1);

返回

col1
====
d
e

- Nick Pierpoint

---

@Gold：我认为您应该接受这个答案，这会帮助未来的访问者直接找到核心问题。 - hagrawal7777

但是在这里声明，任何字符串都与 SQL 中的 null 匹配吗？这有点令人困惑。 - K.Nicholas

这太奇怪了 - 我今天遇到了这个问题。我发誓他们更新了这个函数。 我已经无数次使用（并且更喜欢）“not in”方法。从逻辑上讲，它们应该给出相同的答案。 - GenDemo

好的 - 我的一个小精灵找到了解决方法...这是因为您在表test_b中有空值。如果您注释掉创建空值的插入语句，那么您将看到“not in”起作用了。 - GenDemo

Answer 2

我认为它们有相同的作用。

"not in" 可以使用字面值，而 "not exists" 需要查询来比较结果。

编辑： "not exists" 可能很好用，因为它可以与外部查询连接，并且如果条件使用索引列，则可以导致索引的使用。

编辑2：还可以参考此问题。

编辑3：让我撤回上述内容。 请参见此链接。我认为这完全取决于数据库如何翻译和数据库/索引等。

- shahkalpesh

---

不要忘记对空值的处理：https://dev59.com/x3I-5IYBdhLWcg3wu7Lv#1703712 - Nick Pierpoint

Tom Kyte的链接确实深入核心问题。 - Doug Porter

Answer 3

exists更快，但是性能会有所不同。

最重要的区别在于对null值的处理。你可能会认为使用in和exists查询结果相同，但是当子查询返回null时，你可能会感到惊讶。

你可能会发现存在null值会导致exists查询失败。

请参考Joe Celko的《SQL for smarties》以获得更好的解释何时使用每个查询方式。

- David Roussel

Answer 4

Not in 是用于在一组元素中测试元素的存在性，因此更简单。

Not exists 可以处理更复杂的查询，包括分组（例如具有 sum(x)=z 或具有 count(*)>3 的情况），具有多个条件的结果（例如匹配多个元素），并且可以利用索引。

在某些情况下，not in 比 not exists 更容易实现。我通常发现这是在测试一组值中键字段的值时。

作为经验法则，我更喜欢使用 not exists，因为它涵盖了比 not in 更多的情况。Not exists 可以用于 not in 适用的每种情况，但反之则不行。

- Oliver Townshend