In和exists使用及效能分析(一)：in的使用

space6212發表於2019-06-04

本節主要討論in的原理及使用，以及常見的錯誤的觀點。


一、in的使用
1.1 in的使用
1.1.1 內、外兩表關聯欄位都非空
SQL> select * from tb1;
T1 NAME1
---------- ----------
1 1
2 2
SQL> select * from tb2;
T2 NAME2
---------- ----------
2 2
SQL> select count(1) from tb1 where t1 in (select t2 from tb2);
--T1=2的記錄符合條件，結果符合常規思維
COUNT(1)
----------
1
SQL> select count(1) from tb1 where t1 not in (select t2 from tb2);
--T1=1的記錄符合條件，結果符合常規思維
COUNT(1)
----------
1
1.1.2 當外表關聯欄位無空值、內表關聯列表有空值時
SQL> select * from tb1;
T1 NAME1
---------- ----------
1 1
2 2
SQL> select * from tb2;
T2 NAME2
---------- ----------
2 2
3
SQL> select count(1) from tb1 where t1 in (select t2 from tb2);
--T1=2的記錄符合條件，結果符合常規思維
COUNT(1)
----------
1
SQL> select count(1) from tb1 where t1 not in (select t2 from tb2);
--無記錄符合條件，結果不符合常規思維
COUNT(1)
----------
0
SQL> select count(1) from tb1 where t1 in (select t2 from tb2 where t2 is not null);
--T1=2的記錄符合條件，結果符合常規思維
COUNT(1)
----------
1
SQL> select count(1) from tb1 where t1 not in (select t2 from tb2 where t2 is not null);
--T1=2的記錄符合條件，結果符合常規思維
COUNT(1)
----------
1
1.1.3當外表關聯欄位有空值、內表關聯列表無空值時
SQL> select * from tb1;
T1 NAME1
---------- ----------
1 1
1
2 2
SQL> select * from tb2;
T2 NAME2
---------- ----------
2 2
3 3
SQL> select count(1) from tb1 where t1 in (select t2 from tb2);
--T1=2的記錄符合查詢條件，符合常規思維
COUNT(1)
----------
1
SQL> select count(1) from tb1 where t1 not in (select t2 from tb2);
--只有T1=2的記錄符合查詢條件，與常規思維相悖
COUNT(1)
----------
1
1.1.4 當內、外表的關聯欄位都有空值時
SQL> select * from tb1;
T1 NAME1
---------- ----------
1 1
2 2
3
SQL> select * from tb2;
T2 NAME2
---------- ----------
2 2
3
SQL> select count(1) from tb1 where t1 in (select t2 from tb2);
--只有t1=2的記錄符合條件，與常規思維相悖
COUNT(1)
----------
1
SQL> select count(1) from tb1 where t1 not in (select t2 from tb2);
--沒有符合條件的記錄，與常規思維相悖
COUNT(1)
----------
0
SQL> select count(1) from tb1 where t1 in (select t2 from tb2 where t2 is not null);
--只有t1=2的記錄符合條件，符合常規思維
COUNT(1)
----------
1
SQL> select count(1) from tb1 where t1 not in (select t2 from tb2 where t2 is not null);
--只有t1=1的記錄符合條件，與常規思維相悖
COUNT(1)
----------
1
1.1.5 外表無記錄
SQL> select * from tb1;
T1 NAME1
---------- ----------
SQL> select * from tb2;
T2 NAME2
---------- ----------
2 2
3 3
SQL> select count(1) from tb1 where t1 in (select t2 from tb2);
--無記錄返回
COUNT(1)
----------
0
SQL> select count(1) from tb1 where t1 not in (select t2 from tb2);
--無記錄返回
COUNT(1)
----------
0
這裡很容易理解，無論條件成立與否，因為外表無記錄，索引返回記錄數為0
1.1.6 內表無記錄
SQL> select * from tb1;
T1 NAME1
---------- ----------
1 1
2 2
SQL> select * from tb2;
T2 NAME2
---------- ----------
SQL> select count(1) from tb1 where t1 in (select t2 from tb2);
--所有記錄都不符合條件，結果符合常規思維
COUNT(1)
----------
0
SQL> select count(1) from tb1 where t1 not in (select t2 from tb2);
--所有記錄都符合條件，結果符合常規思維
COUNT(1)
----------
2
1.2 in運算總結：
1.2.1單列作in運算時
1. col in (v1,v2,...vn) 相當於 (col=v1 or col=v2 or ...col=vn)
col not in (v1,v2,...vn) 相當於 (col<>v1 and col<>v2 and ...col<>vn)
2. 當in 列表的記錄數大於0，且in列表(vn)有null值時，則oracle在處理 in 的時候轉換成：
(col=v1 or col=v2 or col=null or ...col=vn)
所以，in列表有null值不會引起與常規思維不一樣的結果
3. 當not in 列表的記錄數大於0，且not in列表有null值時，oracle在處理not in 當時候轉換成：
(col<>v1 and col<>v2 and col<>null and ...col<>vn)，由於oracle中null是一個非常特殊的值，null與任何值做算術運算子比較都返回false，由於多個條件是用and連線的，所以這種情況整個條件是false，也就不會有記錄返回。
這種情況是最容易搞混和不理解的。
4. 當in/not in 列表的記錄數大於0，且col是null值時，null與任何值做算術運算子比較都返回false，根據in和not in的處理方式可知，無論是in運算還是not in 運算，oracle不會有任何記錄返回。也就是說oracle在處理時相當於把col是null的記錄忽略不作處理。
5. 當in/not in 列表的記錄數為0時（注意與in/not in列表全為空不同），in運算相當於條件1=2；not in運算相當於1=1;
6. 在not in操作中，可以在子查詢中加入col is not null使得有記錄返回
1.2.2 多列作in運算時
1. (col1,col2) in ((v11,v12),(v21,v22),...(vn1,vn2))相當於
((col1=v11 and col2=v12) or (col1=v21 and col2=v21) ... or (col1=vn1 and col2=vn2))
(col1,col2) not in ((v11,v12),(v21,v22),...(vn1,vn2))相當於
((col1<>v11 or col2<>v12) and (col1<>v21 or col2<>v22) and ...(col1<>vn1 or col2<>vn2))
2. 多列in運算當原理和單列一樣，把col1和col2看作一個整體col，把vn1和vn2看作一個整體vn就相當於單列作in運算了。
3. 多列運算比較特殊當兩種情況是col1和col2都為null時，相當於單列中都col為null，oracle在處理這種情況時，相當於忽略這條記錄；
當vn1和vn2都為null時，相當於單列運算中vn為null的情況，在作in運算時不會影響返回結果，但在作not in運算時不會有任何記錄返回。
4. 當col1和col2中有一個為null時，作in運算時oracle相當於把該記錄忽略不計，因為(null or null or null...)結果是null。（not in不能忽略）
5. 當col1和col2其中一個為null，且另一個的值在包含在not in列表中時，在作not in運算時oracle相當於把該記錄忽略不計(null && true/false結果是null)。
6. 在not in操作中，可以在子查詢加入((col1 is not null) or (col2 is not null))使得有結果返回
7. 以上小結適用於in/not in 列表的記錄數大於0的情況
8. any、all運算與in類似

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/231499/viewspace-63763/，如需轉載，請註明出處，否則將追究法律責任。

相關文章