共計 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 小編會繼續努力為大家帶來更多實用的文章！
