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

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

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

一.问题重现

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

 

SQL> connscott/tiger;

Connected.

SQL> descemp

 Name                                     Null?    Type

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

 EMPNO                                     NOT NULLNUMBER(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> 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  not  exists  ( 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 的影响。 

1 2 下一页