“NOT_IN”与“NULL”的邂逅

核心提示：DNAME--RESEARCHSALESACCOUNTING而NOT IN一般可以改为NOT EXISTS：SQL> select dname from dept2 where deptno not in(select deptno from emp where deptno is not null)3 /DNA

DNAME
--------------
RESEARCH
SALES
ACCOUNTING

而NOT IN一般可以改为NOT EXISTS：

SQL> select dname from dept
2 where deptno not in(select deptno from emp where deptno is not null)
3 /

DNAME
--------------
OPERATIONS

SQL> select dname from dept
2 where not exists(select * from emp where dept.deptno=emp.deptno)
3 /

DNAME
--------------
OPERATIONS

使用NOT EXISTS，即使子查询中包含NULL值，也会得到正确结果。原因是：

select * from emp where dept.deptno=null不会有返回值，这样，EXISTS(select * from emp where dept.deptno=null)返回的布尔值为false，而 NOT(false)显然为TRUE，其他条件与之作and后，依然得到true。

而在NOT IN子句中，是NOT(deptno=null)，即NOT(unkown)，结果依然为unkown，而unkown被当作false，其他条件与之作and后，最后得到false。

NOT EXISTS(select * from emp where dept.deptno=null) ===>

NOT (false) ===>TRUE

所以子查询中的null不会影响其他的查询结果。

EXISTS可以改为使用表连接语法：

SQL> select dname from dept
2 where exists(select * from emp where emp.deptno=dept.deptno)
3 /

DNAME
--------------
RESEARCH
SALES
ACCOUNTING

SQL> select distinct dname from dept,emp
2 where dept.deptno=emp.deptno
3 /

DNAME
--------------
ACCOUNTING
RESEARCH
SALES

修改为表连接语法后，性能会有所提高，而且可以在select子句中查询另外一个表中的列。

EXISTS还可以改为使用count(*)，因为这时对于每个dept表中的deptno要遍历整个emp表，很明星性能会差：

SQL> select dname from dept
2 where (select count(*) from emp where emp.deptno=dept.deptno)>0
3 /

DNAME
--------------
ACCOUNTING
RESEARCH
SALES

NOT EXISTS可以修改为outer join：

SQL> select dname from dept
2 where not exists(select * from emp where emp.deptno=dept.deptno)
3 /

DNAME
--------------
OPERATIONS

SQL> select dname from dept
2 left outer join emp on dept.deptno=emp.deptno
3 where emp.deptno is null
4 /

DNAME
--------------
OPERATIONS

where emp.deptno is null是左外连接的结果中的emp.deptno为null，而不是指原来的emp表。

EXISTS与NOT EXISTS改为表连接语法后，性能一般都会提高。

下一步再研究这几种语法在执行计划上的差别：

IN与EXISTS的执行计划相同，与table join稍有区别。

NOT EXISTS与左外连接的执行计划相同，与NOT IN稍有区别。

有待于进一步研究其效率上的差异。