Oracle Null 与 in…

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

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

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

SQL> conn scott/tiger;
Connected.

SQL> desc emp

  Name                                                                        Null?      Type
  ------------------------------------------------- -----------------
  EMPNO                                                                  NOT NULL    NUMBER(4)
  ENAME                                                                                        VARCHAR2(10)
  JOB                                                                                            VARCHAR2(9)
  MGR                                                                                            NUMBER(4)
  HIREDATE                                                                                  DATE
  SAL                                                                                            NUMBER(7,2)
  COMM                                                                                          NUMBER(7,2)
  DEPTNO                                                                                      NUMBER(2)

将emp 表复制一份：

SQL> create table 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> select empno,ename from emp where job not in (select job from emp1);
no rows selected
--这里没有返回结果集，正常情况下应该返回我们之前insert的dave。

SQL> select empno,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 not exists ( SELECT * FROM emp1 B where 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> select empno,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

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 or id=3 or id=NULL;

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

select * from t1 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 * from t1 where exists ( select * from t2 where t2.col1 = t1.col1)

相当于：

for  x  in (  select    *    from  t1 )
    loop
            if  (  exists  (  select * from  t2  where t2.col1 =  x.col1))
            then
                OUTPUT THE RECORD in x
            end  if
end  loop

exists ( select * from  t2  where t2.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 not exists ( SELECT * FROM emp1 B where B.job = A.job);

        EMPNO ENAME
--------------------
          8888 Dave

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

我们上边的查询等价于：

for  x  in  (  select    *    from empA )
    loop
            if  (not exists  (  select * from emp2B where B.job =  x.job )
            then
                OUTPUT THE RECORDin x
            end    if
end  loop

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

转自：http://www.cnblogs.com/hibernate315/archive/2011/09/24/2398946.html