Oracle Null 与 in， exists 的关系说明(not in 查不到结果)

同事说查询遇到一个奇怪的事，2个表进行not in 操作没有返回结果，正常情况下应该是有返回的。

一.问题重现

一般来说，问题能重现就是好消息，最怕不能重现。

SQL> connscott/tiger;

Connected.

SQL> descemp

NameNull? Type

------------------------------------------------- -----------------

EMPNO NOT NULLNUMBER(4)

ENAMEVARCHAR2(10)

JOBVARCHAR2(9)

MGRNUMBER(4)

HIREDATE DATE

SALNUMBER(7,2)

COMMNUMBER(7,2)

DEPTNONUMBER(2)

将emp 表复制一份：

SQL> createtable emp1 as select * from emp;

Table created.

我们向emp 表里插入一些值：

SQL> insertinto emp(empno,ename) values(8888,'Dave');

1 row created.

SQL>commit;

Commitcomplete.

这里我们只插入了empno和ename，其他为空。

下面进行2张表的的操作：

SQL> selectempno,ename from emp where job not in (select job from emp1);

no rowsselected

--这里没有返回结果集，正常情况下应该反回我们之前insert 的dave。

SQL> selectempno,ename from emp where job in(select job from emp1);

EMPNO ENAME

--------------------

7934 MILLER

7900 JAMES

7876 ADAMS

7369 SMITH

7844 TURNER

7654 MARTIN

7521 WARD

7499 ALLEN

7782 CLARK

7698 BLAKE

7566 JONES

EMPNO ENAME

--------------------

7902 FORD

7788 SCOTT

7839 KING

14 rowsselected.

换成exists 进行测试：

SQL>select empno,ename from emp A where notexists ( SELECT * FROM emp1 Bwhere B.job = A.job);

EMPNO ENAME

--------------------

8888 Dave

SQL>select empno,ename from emp A where exists( SELECT * FROM emp1 B where B.job = A.job);

EMPNO ENAME

--------------------

7934 MILLER

7900 JAMES

7876 ADAMS

7369 SMITH

7844 TURNER

7654 MARTIN

7521 WARD

7499 ALLEN

7782 CLARK

7698 BLAKE

7566 JONES

EMPNO ENAME

--------------------

7902 FORD

7788 SCOTT

7839 KING

14 rowsselected.

使用exists 不受null 的影响。

二.问题分析

我们在emp 表里查询的记录有空值，并且我们进行not in 和exists 操作时，都是用null 来判断的，如果我们换成非null 字段就可以正常进行操作了。

SQL> selectempno,ename from emp where empno not in (select empno from emp1);

EMPNO ENAME

--------------------

8888 Dave

换成非null 字段就能正常显示了。

2.1 Null 说明

联机文档上的说明如下：

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/sql_elements005.htm#i59110

A condition that evaluates toUNKNOWNacts almost likeFALSE.For example, aSELECTstatement with a condition in theWHEREclausethat evaluates toUNKNOWNreturns no rows. However, a condition evaluatingtoUNKNOWNdiffers fromFALSEin that further operations onanUNKNOWNcondition evaluation will evaluate toUNKNOWN. Thus,NOTFALSEevaluatestoTRUE, butNOTUNKNOWNevaluates toUNKNOWN.

Table 3-20shows examples of various evaluations involving nulls inconditions. If the conditions evaluating toUNKNOWNwere used in aWHEREclauseof aSELECTstatement, then no rows would be returned for that query.

Table 3-20 ConditionsContaining Nulls

Condition	Value of A	Evaluation
a IS NULL	10	FALSE
a IS NOT NULL	10	TRUE
a IS NULL	NULL	TRUE
a IS NOT NULL	NULL	FALSE
a = NULL	10	UNKNOWN
a != NULL	10	UNKNOWN
a = NULL	NULL	UNKNOWN
a != NULL	NULL	UNKNOWN
a = 10	NULL	UNKNOWN
a != 10	NULL	UNKNOWN

2.2 IN 和 NOT IN 判断说明

IN功能上相当于 =ANY 的操作，而NOT IN 功能上相当于 !=ALL 的操作。

IN在逻辑上实际上就是对给定的成员集合或者子查询结果集进行逐条的判定.

如：select * from t1 where id in(1,2,3,NULL);

实际执行的命令等价于：

Select * from t1 where id=1 or id=2 orid=3 or id=NULL;

根据上面的表，Id=NULL 为UNKNOWN。 那么无法查询出列值为Null的记录。即等价于：

Select * from t1 where id=1or id=2 or id=3

NOT IN 的逻辑关系可以理解为：NOT (X=Y OR N=M) 等价于 X!=Y AND N!=M。那么：

select * fromt1 where id not in (1,2,3,NULL);

等价于

Select * fromt1 where id !=1 and id!=2 and id !=3 and id !=NULL

根据上面的NULL 表，id!=NULL 的结果为UNKNOWN。 那么该值为假，所以不管前面的条件真假与否，整个逻辑判断为假，所以没有返回任何记录。

解决方法就是在in 和not in的操作之前先把NULL 过滤掉。

2.3 EXISTS 说明

先看看exists 的执行过程：

select*fromt1whereexists(select*fromt2wheret2.col1=t1.col1)

相当于：

forxin(select*fromt1)
loop
if(exists(select*fromt2wheret2.col1=x.col1))
then
OUTPUTTHERECORDin x
endif
endloop

exists(select*fromt2wheret2.col1=x.col1)返回是一个布尔值，not exists只是对exists子句返回对布尔值取非，这与in和not in是有本质区别的（not in是对in表达式取非，转换成另一种等价表达式）

从上面的逻辑，也可以看出EXISTS语句实际上是通过循环外部查询的结果集，来过滤出符合子查询标准的结果集。于是外部查询的结果集数量对该语句执行性能影响最大，故如果外部查询的结果集数量庞大，用EXISTS语句的性能也不一定就会好很多。

但使用exists 一个很明显的优点，就是如果相关字段有索引的化，会使用索引来进行操作。而不需要进行全表扫描。 当表大的时候，效率肯定是会比in 和not in 高。这也是我们在写SQL 时推荐使用exists的原因。

现在看一下我们之前使用not in 查不到结果，但用not exits 却可以查到：

SQL>select empno,ename from emp A where notexists ( SELECT * FROM emp1 Bwhere B.job = A.job);

EMPNO ENAME

--------------------

8888 Dave

Dave的记录 存在与emp 表，不存在emp1表。

我们上边的查询等价于：

forxin(select*fromempA )
loop
if(notexists(select*fromemp2BwhereB.job=x.job )
then
OUTPUTTHERECORDin x
endif
endloop

这样当我们的X.job 为NULL 时，满足条件，输出了Dave 的记录。

-------------------------------------------------------------------------------------------------------