共计 4898 个字符，预计需要花费 13 分钟才能阅读完成。
这篇文章主要介绍“oracle sql 优化中 not in 子句包含 null 返回结果为空的分析”，在日常操作中，相信很多人在 oracle sql 优化中 not in 子句包含 null 返回结果为空的分析问题上存在疑惑，丸趣 TV 小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”oracle sql 优化中 not in 子句包含 null 返回结果为空的分析”的疑惑有所帮助！接下来，请跟着丸趣 TV 小编一起来学习吧！
创建测试表：
create table t_dept as select * from scott.dept;
create table t_emp as select * from scott.emp;
insert into t_emp(deptno,ename) values(null, MINGSHUO – 在 emp 表中插入一条数据，deptno 列为 null
commit;
数据结构如下：
SQL select distinct deptno from t_emp;
DEPTNO
———-
30
20
10
SQL select distinct deptno from t_dept;
DEPTNO
———-
30
20
40
10
此时发起一条查询，查询不在 emp 中但是在 dept 表中部门信息：
SQL select * from t_dept where deptno not in (select deptno from t_emp where deptno is not null);
DEPTNO DNAME LOC
———- ————– ————-
40 OPERATIONS BOSTON
此时是有结果返回的。
然后把子查询中的 where dept is not null 去掉，再次运行查询：
SQL select * from t_dept where deptno not in (select deptno from t_emp);
no rows selected
此时返回结果为空。
这里很多人存在疑惑，为什么子查询结果集包括 null 就会出问题，比如 t_dept.deptno 为 40 的时候，40 not in
（10,20,30,null）也成立啊。毕竟 oracle 查询优化器不如人脑智能懂得变通，查看执行计划就比较容易明白了。
Execution Plan
———————————————————-
Plan hash value: 2864198334
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 4 | 172 | 5 (20)| 00:00:01 |
|* 1 |
HASH JOIN ANTI NA
| | 4 | 172 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T_DEPT | 4 | 120 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T_EMP | 15 | 195 | 2 (0)| 00:00:01 |
—————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – access(DEPTNO = DEPTNO)
Note
—–
– dynamic sampling used for this statement (level=2)
注意到这里 id 1 是 HASH JOIN ANTI NA。这时候就想起来了，not in 是对 null 值敏感的。所以普通反连接是不能处理 null 的，因此 oracle 推出了改良版的能处理 null 的反连接方法，这种方法被称为 Null-Aware Anti Join。operation 中的关键字 NA 就是这么来的了。
在 Oracle 11gR2 中，Oracl 通过受隐含参数_OPTIMIZER_NULL_AWARE_ANTIJOIN 控制 NA，其默认值为 TRUE，表示启用 Null-Aware Anti Join。
下面禁用掉，然后再观察：
alter session set _optimizer_null_aware_antijoin = false;
再次执行：select * from t_dept where deptno not in (select deptno from t_emp);
执行计划如下：
Execution Plan
———————————————————-
Plan hash value: 393913035
—————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 30 | 4 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL| T_DEPT | 4 | 120 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| T_EMP | 14 | 182 | 2 (0)| 00:00:01 |
—————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – filter(NOT EXISTS (SELECT 0 FROM T_EMP T_EMP WHERE
LNNVL(DEPTNO :B1)))
3 – filter(LNNVL( DEPTNO :B1))
Note
—–
– dynamic sampling used for this statement (level=2)
lnnvl 用于某个语句的 where 子句中的条件，如果条件为 true 就返回 false；如果条件为 UNKNOWN 或者 false 就返回 true。该函数不能用于复合条件如 AND, OR, or BETWEEN 中。
此时比如 t_dept.deptno 为 40 的时候，（40 not in 10）and（40 not in 20）and（40 not in 30）and（40 not in null），注意这里是 and“并且”，条件都需要满足。
结果是 true and true and true and false 或者 unknow。经过 lvnnvl 函数后：
false and false and false and true, 结果还是 false。所以自然就不会有结果了。
如果还不明白的话换个比较直观的写法：
SQL select * from t_dept where deptno not in (10,20,null);
no rows selected
Execution Plan
———————————————————-
Plan hash value: 719542577
—————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 30 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_DEPT | 1 | 30 | 2 (0)| 00:00:01 |
—————————————————————————-
Predicate Information (identified by operation id):
—————————————————
1 – filter(DEPTNO 10 AND DEPTNO 20 AND
DEPTNO TO_NUMBER(NULL))
Note
—–
– dynamic sampling used for this statement (level=2)
过滤条件 DEPTNO 10 AND DEPTNO 20 AND DEPTNO TO_NUMBER(NULL) 因为最后一个 and 条件，整个条件恒为 flase 或者 unkonw。
所以 not in 的子查询中出现 null 值，无返回结果。
这种时候其实可以用 not exists 写法和外连接代替：
not exists 写法：
其实这种写法前面已经出现过了。就在禁用掉反连接之后，出现在 fileter 中的，oracle 在内部改写 sql 时可能就采用了这种写法：
select *
from t_dept d
where not exists (select 1 from t_emp e where d.deptno = e.deptno);
外连接的写法：
select d.* from t_dept d, t_emp e where d.deptno=e.deptno(+) and e.deptno is null;
同事还给我展示了丁俊的实验，里面有复合列的讨论，结论简单明了，这里我就直接搬过来吧，如下：
/**
根据 NULL 的比较和逻辑运算规则，OR 条件有一个为 TRUE 则返回 TRUE，全为 FALSE 则结果为 FALSE，其他为 UNKNOWN，比如
(1,2) not in (null,2) 则相当于 1 null or 2 2, 那么明显返回的结果是 UNKNOWN，所以不可能为真，不返回结果，但是
(1,2) not in (null,3) 相当于 1 null or 2 3, 因为 2 3 的已经是 TRUE, 所以条件为 TRUE，返回结果，也就说明了为什么 Q2 中的
测试是那样的结果
**/
看个简单的结果：
SQL SELECT * FROM DUAL WHERE (1,2) not in ((null,2) );
DUMMY
—–
SQL SELECT * FROM DUAL WHERE (1,2) not in ((null,3) );
DUMMY
—–
X
到此，关于“oracle sql 优化中 not in 子句包含 null 返回结果为空的分析”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注丸趣 TV 网站，丸趣 TV 小编会继续努力为大家带来更多实用的文章！
