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。