NOT EXISTS and NOT IN区别

The NOT EXISTS and NOT IN Constructs

我们将讨论如何评估NOT EXISTS ，NOT IN ，使用Oracle的反连接访问路径的先决条件，以及影响反连接查询优化提示。 然后我们将 看看 反连接 访问路径如何 可以用来做 一些查询 更有效的 几个例子 。


NOT EXISTS and NOT IN 用来检索表中的行为不匹配另一个表中的数据。 在早期，Oracle将使用一个NOT EXISTS and NOT IN 子查询。 对于每个候选行的查询的主体，Oracle将执行子查询。 如果子查询检索一行或多行，然后在候选行会被丢弃。 从概念上讲 这个方法看起来 类似于 嵌套循环的 反连接 。

//not in和not exists也是根据第一个表的数据，在第二个表中进行匹配，只不过是一旦有一个以上的匹配，第一个表的数据就不会显示。

不同于in和exists（两者相同），not in和not exists在处理null值上不同。如果not in返回至少一个null，那么not in就返回flase，第一个表的数据行就不会显示。
对于not exists，只是关心是否返回行，因此返回的无论是null还是非null，都是true，只要有数据返回。

显示没有员工的部门信息。


        SELECT   D.deptno, D.dname
        FROM     dept D
        WHERE    NOT EXISTS
                 (
                 SELECT 1
                 FROM   emp E
                 WHERE  E.deptno = D.deptno
                 )
        ORDER BY D.deptno;
Written with a NOT IN clause, the query becomes:


        SELECT   D.deptno, D.dname
        FROM     dept D
        WHERE    D.deptno NOT IN
                 (
                 SELECT E.deptno
                 FROM   emp E
                 )
        ORDER BY D.deptno;
这是一个无关子查询，如果子查询中有null返回，那么这个查询的返回总是空，不会返回任何数据。
但是not exists可以正确返回没有职员的部门。
因此在使用not in的时候一定要注意。

使用not in还是not exists，不仅仅是功能上不同，而且在性能上也有很大的不同。
NOT IN会骗过子查询中所涉及表的索引。

//如果子查询中的列上有not null约束，或者查询中有where子句（去除了null），那么not in可以走索引，效果和not exists效果一样。这一点很重要。
如果我们想写的查询显示空的部门在条款，给出相同的结果作为一个不存在的版本不，我们需要添加一个谓词来查询，排除空值。结果查询：

        SELECT   D.deptno, D.dname
        FROM     dept D
        WHERE    D.deptno NOT IN
                 (
                 SELECT E.deptno
                 FROM   emp E
                 WHERE  E.deptno IS NOT NULL
                 )
        ORDER BY D.deptno;

确定是使用not exists还是使用not in？关键是看你要怎样处理null值。
如果对于null值，你总是希望返回false，那么应该使用not in。
否则，就可以使用not exists，或者使用not in（需要使用where去除null；或者加上not null约束）。
因此，你必须先考虑怎么来处理空值。 如果你需要在子查询一个空值的存在使表达都不成立，那么你需要使用不构成。 否则 你们 之间的选择 是不存在的 ， 不在 （ 与额外的 谓词 如果子查询 会 返回一个空 值 的能力 ） 。

.如果你不需要关注null值的处理。那么你就应该关注是nested还是hash，或者sort merge。
对于第一个表的谓词列，选择性很高，子查询的表的连接列上有选择性很高的索引的时候，系统会优先选择nested loop。
第一个表的谓词选择性很低，第二个表中的连接列的选择性很低，那么系统只能选择hash或者merge。


如果我们不想使用filter、nested loops anti-join，那么我们可以多考虑使用not in，而不是not exists。但是有时候，oracle可能会更加喜欢not exists。
总之是出了问题要看执行计划。

通常我们不想使用filter，但是有可能filer的效果更好，因为filter在处理子查询的时候，当碰到第一个匹配的结果的时候，就结束这一次的子查询，进行下一个。因此效果可能也挺好，具体还是要看执行统计信息。
如果执行计划不是很准，就进行trace，然后tkprof，这一个很好的习惯，特别是在具体分析sql语句的时候。
我们在c/s的时候经常使用，在b/s中，照样可用。


1、必须首先确认业务上需要如何处理null值，然后确定使用not in还是not exists。
2、下一步就是这个子查询是否受益于merge、hash join，那么你可能需要使用not in。
3、如果你希望使用not in，但是不希望因为null的问题导致结果是false，那么你要确认返回的子查询结果没有null值。
4、如果感觉不能受益于merge、hash join，而且也不需要not in的特殊语义，那么可以选择使用not exists。
这个时候，oracle可能会选择使用效率更高的filter，而不是nested loops anti-join。