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

space6212發表於2019-03-19

本節主要討論exists的使用。


二、exists的使用
2.1 exists的使用
2.1.1內、外兩表關聯欄位都非空
SQL> select * from tb1;
T1 NAME1
---------- ----------
1 1
2 2
SQL> select * from tb2;
T2 NAME2
---------- ----------
2 2
3 3
SQL> select count(1) from tb1 where exists (select 1 from tb2 where tb1.t1=tb2.t2);
COUNT(1)
----------
1
SQL> select count(1) from tb1 where not exists (select 1 from tb2 where tb1.t1=tb2.t2);
COUNT(1)
----------
1
結果與一般思維相符，沒什麼好說的。
2.1.2 當外表關聯欄位無空值、內表關聯列表有空值時
SQL> select * from tb1;
T1 NAME1
---------- ----------
1 1
2 2
SQL> select * from tb2;
T2 NAME2
---------- ----------
2
2
3 3
SQL> select count(1) from tb1 where exists (select 1 from tb2 where tb1.t1=tb2.t2);
--T1=2符合條件，返回結果與預期相符
COUNT(1)
----------
1
SQL> select count(1) from tb1 where not exists (select 1 from tb2 where tb1.t1=tb2.t2);
--T1=1符合條件，返回結果與預期相符
COUNT(1)
----------
1
2.1.3當外表關聯欄位有空值、內表關聯列表無空值時
SQL> select * from tb1;
T1 NAME1
---------- ----------
1 1
2 2
3
SQL> select * from tb2;
T2 NAME2
---------- ----------
2 2
SQL> select count(1) from tb1 where exists (select 1 from tb2 where tb1.t1=tb2.t2);
--符合常規邏輯
COUNT(1)
----------
1
SQL> select count(1) from tb1 where not exists (select 1 from tb2 where tb1.t1=tb2.t2);
--符合常規邏輯，注意與in的區別
COUNT(1)
----------
2
2.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 exists (select 1 from tb2 where tb1.t1=tb2.t2);
--只有T1=2的記錄符合條件，與常規思維相悖
COUNT(1)
----------
1
SQL> select count(1) from tb1 where not exists (select 1 from tb2 where tb1.t1=tb2.t2);
--T1=1和T1 為NULL的記錄都返回了，與常規思維相悖
COUNT(1)
----------
2
SQL> select count(1) from tb1 where exists (select 1 from tb2 where tb1.t1=tb2.t2 and t2 is not null);
--只返回T1=2的記錄符合常規邏輯
COUNT(1)
----------
1
SQL> select count(1) from tb1 where not exists (select 1 from tb2 where tb1.t1=tb2.t2 and t2 is not null);
-- T1=1和T1 為NULL的記錄都返回了，符合常規邏輯
COUNT(1)
----------
2
2.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 exists (select 1 from tb2 where tb1.t1=tb2.t2);
COUNT(1)
----------
0
SQL> select count(1) from tb1 where not exists (select 1 from tb2 where tb1.t1=tb2.t2);
COUNT(1)
----------
0
這個很好理解，源表無記錄，無論條件真假，都不會有記錄返回的。
2.1.6內表無符合條件記錄
SQL> select * from tb1;
T1 NAME1
---------- ----------
1 1
2 2
3
SQL> select * from tb2;
T2 NAME2
---------- ----------
SQL> select count(1) from tb1 where exists (select 1 from tb2 where tb1.t1=tb2.t2);
--符合常規邏輯
COUNT(1)
----------
0
SQL> select count(1) from tb1 where not exists (select 1 from tb2 where tb1.t1=tb2.t2);
--符合常規邏輯
COUNT(1)
----------
3
2.2 exists使用總結
1. exists的原理
select * from tb1 where exists ( select null from tb2 where col1 = col2 )
相當於：
for t1 in ( select * from tb1 )
loop
if ( exists ( select 1 from tb2 where t2 = tb1.col1 )
then
OUTPUT THE RECORD
end if
end loop
其中：exists ( select null from tb2 where t2 = tb1.col1 )返回是一個布林值，not exists只是對exists子句返回對布林值取非，這與in和not in是有本質區別的（not in是對in表示式取非，轉換成另一種等價表示式）
2. exists運算中，當t2列表中有空值時，得到結果與把空值從列表中去掉是一樣當，也就是說，可以把col2列表的空值忽略。
3. 只需記住null=null和null<>null在oracle都不成立，即可理解exists/not exists運算不符合常規思維的地方。

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

相關文章