SQL “不存在”和“不存在”有什么区别？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1699424/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What's the difference between 'not in' and 'not exists'?
提问by Gold
What's the difference between not inand not existsin an Oracle query?
Oracle 查询中的not in和之间有什么区别not exists？
When do I use not in? And not exist?
我not in什么时候使用？和not exist？
采纳答案by shahkalpesh
I think it serves the same purpose.
我认为它有同样的目的。
not incan alsotake literal values whereas not existsneed a query to compare the results with.
not in也可以采用文字值，而not exists需要查询来比较结果。
EDIT: not existscould be good to use because it can joinwith the outer query & can lead to usage of index, if the criteria uses column that is indexed.
编辑：not exists可能很好用，因为它可以join与外部查询一起使用，并且如果条件使用被索引的列，则可以导致使用索引。
EDIT2: See thisquestion as well.
EDIT2：也请参阅此问题。
EDIT3: Let me take the above things back.
See thislink. I think, it all depends on how the DB translates this & on database/indexes etc.
EDIT3：让我把上面的东西拿回来。
请参阅此链接。我认为，这完全取决于数据库如何在数据库/索引等上翻译它。
回答by Nick Pierpoint
The difference between NOT INand NOT EXISTSbecomes clear where there are NULLvalues included in the result.
NOT IN和NOT EXISTS之间的区别在NULL结果中包含值的地方变得很明显。
For example:
例如：
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);
Note: They key difference is that test_bcontains a nullvalue.
注意：它们的主要区别在于test_b包含一个null值。
select * from test_a where col1 not in (select col1 from test_b);
No rows returned
没有返回行
select * from test_a where
not exists
(select 1 from test_b where test_b.col1 = test_a.col1);
Returns
退货
col1
====
d
e
回答by David Roussel
There can be performance differences, with exists being faster.
可能存在性能差异，并且存在更快。
The most important difference is the handling of nulls. Your query might seem to work the same with both in and exists, but when your sub-query returns null you might get a shock.
最重要的区别是对空值的处理。您的查询似乎对 in 和 exists 都有效，但是当您的子查询返回 null 时，您可能会感到震惊。
You might find that the existence of nulls causes exists to fail.
您可能会发现空值的存在会导致存在失败。
See Joe Celko's 'SQL for smarties' for a better explanation of when to use each.
请参阅 Joe Celko 的 'SQL for smarties' 以更好地解释何时使用每个。
回答by Oliver Townshend
Not in is testing for the present of an element in a set of elements, so it is simpler.
Not in 是测试一个元素在一组元素中是否存在，所以它更简单。
Not exists can handle more complicated queries, including grouping (eg having sum(x)=z or having count(*)>3), results with multiple conditions (eg matching multiple elements), and can take advantage of indexes.
Not exists 可以处理更复杂的查询，包括分组（例如具有 sum(x)=z 或具有 count(*)>3）、具有多个条件的结果（例如匹配多个元素），并且可以利用索引。
In some situations not in is easier to do than not exists. I generally find this is where I am testing for the value of a key field in set of values.
在某些情况下，不存在比不存在更容易做到。我通常发现这是我测试值集中关键字段的值的地方。
As a rule of the thumb, I prefer not exists as it covers a lot more situations than not in. Not exists can be used for every situation that not in is used for, but not the reverse.
根据经验，我更喜欢不存在，因为它涵盖的情况比不存在的情况多得多。不存在可用于所有不存在的情况，但反过来不行。
