NOT IN的坑——子查询含空值

一：环境

SQL> select * from v$version;
BANNER
----------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

drop table emp;
drop table dept;
create table emp as select * from scott.emp;
create table dept as select * from scott.dept;

insert into emp values(1111,'abcd','clear',sysdate,1000,null,null);
commit;

SQL> select * from emp;
 EMPNO ENAME    JOB          MGR HIREDATE       SAL       COMM DEPTNO
------ -------- ---------- ----- ------------ ----- ---------- ------
  7369 SMITH    CLERK       7902 17-DEC-80      800                20
  7499 ALLEN    SALESMAN    7698 20-FEB-81     1600        300     30
  7521 WARD     SALESMAN    7698 22-FEB-81     1250        500     30
  7566 JONES    MANAGER     7839 02-APR-81     2975                20
  7654 MARTIN   SALESMAN    7698 28-SEP-81     1250       1400     30
  7698 BLAKE    MANAGER     7839 01-MAY-81     2850                30
  7782 CLARK    MANAGER     7839 09-JUN-81     2450                10
  7788 SCOTT    ANALYST     7566 19-APR-87     3000                20
  7839 KING     PRESIDENT        17-NOV-81     5000                10
  7844 TURNER   SALESMAN    7698 08-SEP-81     1500          0     30
  7876 ADAMS    CLERK       7788 23-MAY-87     1100                20
  7900 JAMES    CLERK       7698 03-DEC-81      950                30
  7902 FORD     ANALYST     7566 03-DEC-81     3000                20
  7934 MILLER   CLERK       7782 23-JAN-82     1300                10
  1111 abcd     clear            01-FEB-20     1000


SQL> select * from dept;

    DEPTNO DNAME                        LOC
---------- ---------------------------- --------------------------
        10 ACCOUNTING                   NEW YORK
        20 RESEARCH                     DALLAS
        30 SALES                        CHICAGO
        40 OPERATIONS                   BOSTON

二：查询没有员工的部门		
-- NOT IN写法,无结果,非正确结果
/*
	NOT IN 对NULL敏感，如果子查询有空值，则没有返回结果
*/
SQL> select * from dept where deptno not in (select deptno from emp);

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 2100826622

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    23 |     8  (13)| 00:00:01 |
|*  1 |  HASH JOIN ANTI NA |      |     1 |    23 |     8  (13)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    15 |    45 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("DEPTNO"="DEPTNO")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        466  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

/*
	NOT EXISTS对NULL 不敏感，
*/
SQL> select * from dept where not exists (select 1 from emp where emp.deptno=dept.deptno);

DEPTNO DNAME                        LOC
------ ---------------------------- --------------------------
    40 OPERATIONS                   BOSTON


Execution Plan
----------------------------------------------------------
Plan hash value: 474461924

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    23 |     8  (13)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |      |     1 |    23 |     8  (13)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    80 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |    14 |    42 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
   3 - filter("EMP"."DEPTNO" IS NOT NULL)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        674  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
		  
1:NOT IN 和 <>ALL 对NULL敏感,当子查询有空值时，返回结果为空！
2:NOT IN 通常可以使用NOT EXISTS改写，NOT IN 可能和NOT EXISTS不等价