“不存在”和“不存在”的区别是什么?

What's the difference between not in and not exists in an Oracle query?

在Oracle查询中不存在和不存在的区别是什么?

When do I use not in? And not exist?

我什么时候用not in?而不存在吗?

4 个解决方案

#1

I think it serves the same purpose.

我认为它有同样的作用。

not in can also take literal values whereas not exists need a query to compare the results with.

not in也可以取文字值，而不存在则需要查询来比较结果。

EDIT: not exists could be good to use because it can join with the outer query & can lead to usage of index, if the criteria uses column that is indexed.

编辑:不存在可以很好地使用，因为它可以连接到外部查询&如果标准使用了索引的列，则可以导致索引的使用。

EDIT2: See this question as well.

看看这个问题。

EDIT3: Let me take the above things back.
See this link. I think, it all depends on how the DB translates this & on database/indexes etc.

让我把上面的东西拿回来。看到这个链接。我认为，这一切都取决于DB如何翻译这个，以及数据库/索引等等。

#2

The difference between NOT IN and NOT EXISTS becomes clear where there are NULL values included in the result.

不存在和不存在的区别在结果中包含空值的地方变得很明显。

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_b contains a null value.

注意:它们的关键区别在于test_b包含一个空值。

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

#3

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.

不存在可以处理更复杂的查询，包括分组(如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.

根据经验，我宁愿不存在，因为它涵盖了比不存在更多的情况。不存在可以用于任何不被使用的情况，而不是相反的情况。

#4

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和exist都是相同的，但是当子查询返回null时，您可能会感到震惊。

You might find that the existence of nulls causes exists to fail.

您可能会发现null的存在会导致失败。

See Joe Celko's 'SQL for smarties' for a better explanation of when to use each.

请参阅Joe Celko的“SQL for smarties”来了解何时使用它们的更好解释。

#1