很多时候，我们在SQL语句编写中会遇到很多诡异的情况，明明语义描述无误但是执行结果却是离题千里。面对这些“陷阱”，我们要进行冷静的分析比较，并不断进行知识积累，才能处理好这些问题。本篇主要简述一个笔者遇到的案例。
1、 环境说明准备
在Oracle 11gR2下，构建数据表emp和dept。两者通过department_id进行关联。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 – Production
从数据表employees和departments中获取数据源。
SQL> create table dept as select department_id, department_name from departments;
Table created
SQL> desc dept
Name Type Nullable Default Comments
--------------- ------------ -------- ------- --------
DEPARTMENT_ID NUMBER(4)
DEPARTMENT_NAME VARCHAR2(30) Y
SQL> select count(*) from dept;
COUNT(*)
----------
27
SQL> create table emp as select employee_id, first_name, last_name, department_id from employees;
Table created
SQL> select count(*) from emp;
COUNT(*)
----------
107
SQL> desc emp;
Name Type Nullable Default Comments
------------- ------------ -------- ------- --------
EMPLOYEE_ID NUMBER(6)
FIRST_NAME VARCHAR2(20) Y
LAST_NAME VARCHAR2(25) Y
DEPARTMENT_ID NUMBER(4) Y
SQL> select count(distinct(department_id)) from emp;
DEPARTMENT_ID
-------------
12
1 rows selected
当前员工的部门数量显然是大大小于总部们的数量，说明存在一些部门是没有员工与之相对应的。
2、问题出现
需求是获取当前没有员工的部门列表。一个看似简单的SQL描述如下：
SQL> select * from departments where department_id not in (select department_id from emp);
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
一个部门也没有，显然和数据情况是不相符合的。从语义上看，当前SQL也是没有任何问题的。如果换一个角度，将not in转而使用in，获取有员工的部门。
SQL> select * from departments where department_id in (select distinct department_id from emp);
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
10 Administration 200 1700
（篇幅原因，有删节……）
110 Accounting 205 1700
11 rows selected
有结果返回。从现象上看，not in和in好像不能构成补集关系。而这样问题是在not in的时候表现出来。
3、分析过程
笔者一时也不能作出判断，只能采用抽丝剥茧的方法寻找线索。
SQL> select distinct department_id from emp;
DEPARTMENT_ID
-------------
100
30
20
70
90
110
50
40
80
10
60
12 rows selected
一个诡异的结果，我们能看到的结果集合是11行，而反馈认为结果是12行。难道是因为null因素在其中影响结果？
SQL> select distinct nvl(department_id,999) from emp;
NVL(DEPARTMENT_ID,999)
----------------------
100
30
999
20
（篇幅原因，省略）
60
12 rows selected
果然在emp数据表的department_id列上存在空值，也就是说该列是不能保证非空的。从业务上看，这样也是能说通，就是一个员工暂时没有部门隶属。
很多Oracle问题都是在空值的作用下发生的，这次是不是也是如此？既然是空值，也就不需要关心了，考虑剔除掉空值。
SQL> select * from departments where department_id not in (select distinct department_id from emp where department_id is not null);
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
120 Treasury 1700
（篇幅原因，省略……）
250 Retail Sales 1700
260 Recruiting 1700
270 Payroll 1700
16 rows selected
结果正确！说明看来真的是由于null值的影响。
4、解决方案
看来问题真的是由于null值与not in之间的冲突造成的空结果集合问题。我们从执行计划上看，造成这个问题的原因。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2830588537
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27 | 918 | 6 (17
| 1 | MERGE JOIN ANTI NA | | 27 | 918 | 6 (17
| 2 | SORT JOIN | | 27 | 567 | 2 (0
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 567 | 2 (0
| 4 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0
|* 5 | SORT UNIQUE | | 107 | 1391 | 4 (25
| 6 | TABLE ACCESS FULL | EMP | 107 | 1391 | 3 (0
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
filter("DEPARTMENT_ID"="DEPARTMENT_ID")
22 rows selected
注意，对not in而言，使用通常的内部操作方式是使用空值敏感（Null-Aware）反链接（ANTI NA）方法。在本例中，Oracle Query Optimizer选择的是Merge Join Anti反连接操作。Merge Join基本连接的方法，在笔者之前的blog中有所探讨（http://space.itpub.net/17203031/viewspace-697012）。ANTI反连接操作就是将原来的等号条件替换为不等号条件。
我们认为问题就出现在这里：当是一般连接时候，条件是等于，如果Oracle发现了连接相等条件就认为是成功了。如果存在null值，进行比较的时候，会判定为不可预知结果，是不会作为一般连接结果的。普通连接的时候，只要多于一个配对连接条件符合，那么就返回结果。
而在反连接中，要求所有的数据行匹配都不符合结果。对null列的操作是没有明确的结果判断的，所以认为永远都不存在所有不符合结果的情况。于是，在连接列存在空值的时候，反链接操作返回空结果集合。
而in操作的执行计划完全不是这样。
SQL> explain plan for select * from departments where department_id in (select department_id from emp);
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 315376201
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 374 | 6 (17)
| 1 | MERGE JOIN SEMI | | 11 | 374 | 6 (17)
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 567 | 2 (0)
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)
|* 4 | SORT UNIQUE | | 107 | 1391 | 4 (25)
| 5 | TABLE ACCESS FULL | EMP | 107 | 1391 | 3 (0)
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPARTMENT_ID"="DEPARTMENT_ID")
filter("DEPARTMENT_ID"="DEPARTMENT_ID")
22 rows selected
注意这里使用了merge join半连接操作，比较方式还都是等号条件，满足连接的也是存在一条符合条件的记录即可。
此外，剔除null进行not in不是该问题的唯一解决方案，可以使用not exists来进行替代，也可以获取到正确结果。
SQL> select * from departments a where not exists (select * from emp where department_id=a.department_id);
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
120 Treasury 1700
（篇幅原因，有省略……）
270 Payroll 1700
16 rows selected
此时的执行计划是：
SQL> explain plan for select * from departments a where not exists (select * from emp where department_id=a.department_id);
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 370283621
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27 | 918 | 6 (17)
| 1 | MERGE JOIN ANTI | | 27 | 918 | 6 (17)
| 2 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 27 | 567 | 2 (0)
| 3 | INDEX FULL SCAN | DEPT_ID_PK | 27 | | 1 (0)
|* 4 | SORT UNIQUE | | 107 | 1391 | 4 (25)
| 5 | TABLE ACCESS FULL | EMP | 107 | 1391 | 3 (0)
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPARTMENT_ID"="A"."DEPARTMENT_ID")
filter("DEPARTMENT_ID"="A"."DEPARTMENT_ID")
22 rows selected
虽然也是反链接，但是没有NA（Null Aware），就可以一定程度上缓解出现错误的机会。
5、结论
本篇我们看到了一个由于空值引发的问题。当空值和not in在一起时，就会出现这样的错误。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/17203031/viewspace-708152/ ，如需转载，请注明出处，否则将追究法律责任。
