Exists和IN的原理解析
select * from A where id in(select id from B)
select a.* from A a where exists(select 1 from B b where a.id=b.id)
1.子查詢集小即B表比A表資料小，用IN（子查詢是內表即匹配表，父查詢是外表即驅動表）
2.子查詢集大即B表比A表資料大，用EXISTS（子查詢是內表即匹配表，父查詢是外表即驅動表）
3.當A表資料與B表資料一樣大時,in與exists效率差不多,可任選一個使用.
這樣的結論在11G時是完全錯誤的觀點。在8i、9i時代，可能是正確的，11G的CBO已經最佳化了IN,EXISTS的區別，所以可能SQL寫法不同，但是執行計劃卻是完全一樣的。
IN
IN Condition
An in_condition is a membership condition. It tests a value for membership in a list of values or subquery
select * from A where id in(select id from B)
以上查詢使用了in語句,它查出B表中的所有id欄位並快取起來.之後,檢查A表的id是否與B表中的id相等,如果相等則將A表的記錄加入結果集中,直到遍歷完A表的所有記錄.
它的查詢過程類似於以下過程
List resultSet=[];
Array A=(select * from A);
Array B=(select id from B);
for(int i=0;i<a.length;i++) {=""
for(int j=0;j<b.length;j++){
if(A[i].id==B[j].id) {
resultSet.add(A[i]);
break; } } }
return resultSet;
子查詢有null值時，not in (子查詢)的結果是null，而in (子查詢)的結果不會這樣
SELECT distinct emp.employee_id FROM employees emp order by 1;--107行，沒有null的行
SELECT distinct mgr.manager_id FROM employees mgr order by 1;--19行，最後一行是null
SELECT emp.employee_id,emp.last_name FROM employees emp WHERE emp.employee_id not IN (SELECT mgr.manager_id FROM employees mgr);--0行結果
SELECT emp.employee_id,emp.last_name FROM employees emp WHERE emp.employee_id not IN (SELECT mgr.manager_id FROM employees mgr where mgr.manager_id is not null);--89行結果
SELECT emp.employee_id,emp.last_name FROM employees emp WHERE emp.employee_id IN (SELECT mgr.manager_id FROM employees mgr);--18行結果
SELECT emp.employee_id,emp.last_name FROM employees emp WHERE emp.employee_id IN (SELECT mgr.manager_id FROM employees mgr where mgr.manager_id is not null);--18行結果
where條件的欄位如果值有null，如果where後面還有not in子查詢，則會忽略外查詢中null的查詢結果
select * from employees where DEPARTMENT_ID not in (select DEPARTMENT_ID from departments);--沒有結果
select * from employees where nvl(DEPARTMENT_ID,1) not in (select DEPARTMENT_ID from departments);--有DEPARTMENT_ID為null的那一行
select * from employees where DEPARTMENT_ID in (select DEPARTMENT_ID from departments);--106行記錄
select * from employees where nvl(DEPARTMENT_ID,1) in (select DEPARTMENT_ID from departments)--106行記錄
select department_id from departments order by 1;--27行，沒有null
SELECT department_id FROM employees order by 1;--107行，最後一行是null
SELECT employee_id, last_name FROM employees e,departments d where e.department_id=d.department_id ORDER BY d.department_name;--結果為106行，employee_id=178這行的department_id欄位是null值，不能用於=比較
SELECT employee_id, last_name FROM employees e ORDER BY (SELECT department_name FROM departments d WHERE e.department_id = d.department_id);--結果為107行，employee_id=178這行的department_id欄位是null值，所以排最後
exists
EXISTS Condition
An EXISTS condition tests for existence of rows in a subquery.
TRUE if a subquery returns at least one row.
If a subquery row value is found:
--The search does not continue in the inner query
--The condition is flagged TRUE
如果有一個子查詢結果找到：
--子查詢不再繼續
--條件被標誌為TRUE
（意思是，當子查詢匹配到了一條記錄，就把條件標誌為TRUE，不再需要去遍歷完子查詢）
If a subquery row value is not found:
--The condition is flagged FALSE
--The search continue in the inner query
如果子查詢記錄值沒找到：
--條件被標誌為FALSE
--子查詢繼續
（意思是，當子查詢都遍歷完了還沒有找到的話，則把條件標誌為FALSE）
select a.* from A a where exists(select 1 from B b where a.id=b.id)
以上查詢使用了exists語句,exists()會執行A.length次,它並不快取exists()結果集,因為exists()結果集的內容並不重要,重要的是結果集中是否有記錄,如果有則返回true,沒有則返回false.
它的查詢過程類似於以下過程
List resultSet=[];
Array A=(select * from A)
for(int i=0;i<a.length;i++) {=""
if(exists(A[i].id) { //執行select 1 from B b where b.id=a.id是否有記錄返回
resultSet.add(A[i]);
}
}
return resultSet;
結論：
EXISTS用於檢查子查詢是否至少會返回一行資料，EXISTS(包括 NOT EXISTS )子句的返回值是一個BOOLEAN值。
EXISTS有一個子查詢語句(SELECT XX FROM YY)，根據其子查詢語句的結果集是否最少有一行記錄，返回一個布林值，就算結果集返回為一行但是數值為null，也算返回了資料，返回一個true，比如子查詢為select null from dual的情況。
一種通俗的理解為：
將外查詢表的每一行A，代入子查詢作為檢驗，如果子查詢返回的結果產生了任意一條記錄，則EXISTS子句返回TRUE，這一行A則可作為外查詢的結果行，否則不能作為結果。
Exist：前面查詢的列不一定都要在子查詢中出現
IN：子查詢中的列必須匹配where條件的列
exists如果後面子查詢和父查詢有關聯
則如下
exists （sql 返回結果集為真）
not exists (sql 不返回結果集為真）
exists如果後面子查詢和父查詢沒有關聯（當然現實中沒人會這樣寫SQL，下面的案例只是為了理解原理而舉的例子）
則子查詢結果類似一個true和false，結果為true執行父查詢得出父查詢所有結果，結果為false，不執行父查詢返回0行記錄
具體實驗：
create table t101 (hid number,hname varchar2(10));
insert into t101 values(1,'name1');
insert into t101 values(2,'name2');
insert into t101 values(3,'name3');
create table t102 (hid number,hname varchar2(10));
insert into t102 values(2,'name2');
insert into t102 values(3,'name3');
insert into t102 values(4,'name4');
insert into t102 values(5,'name5');
insert into t102 values(6,'name6');
SQL> select * from t101;
HID HNAME
---------- ----------
1 name1
2 name2
3 name3
SQL> select * from t102;
HID HNAME
---------- ----------
2 name2
3 name3
4 name4
5 name5
6 name6
select * from t101 where exists (select hid from t102 where t101.hid=t102.hid);
執行結果為
2 name2
3 name3
原因可以按照如下分析（只把父查詢表中的值帶入子查詢即可，案例即只有1、2、3）
SELECT * FROM t101 WHERE EXISTS (SELECT hid FROM t102 WHERE t102.hid =1)
---> SELECT hid FROM t102 WHERE t102.hid =1沒有值不返回所以沒有資料
SELECT * FROM t101 WHERE EXISTS (SELECT hid FROM t102 WHERE t102.hid =2)
---> SELECT hid FROM t102 WHERE t102.hid =2有值返回真所以有資料
SELECT * FROM t101 WHERE EXISTS (SELECT hid FROM t102 WHERE t102.hid =3)
---> SELECT hid FROM t102 WHERE t102.hid =3有值返回真所以有資料
NOT EXISTS 就是反過來
select * from t101 where not exists (select hid from t102 where t101.hid=t102.hid);
執行結果為
1 name1
SQL> select * from t101 where exists (select hid from t102 where t101.hid=t102.hid);
HID HNAME
---------- ----------
2 name2
3 name3
SQL> select * from t101 where hid in (select hid from t102 where t101.hid=t102.hid);
HID HNAME
---------- ----------
2 name2
3 name3
SQL> select * from t101 where not exists (select hid from t102 where t101.hid=t102.hid);
HID HNAME
---------- ----------
1 name1
SQL> select * from t101 where hid not in (select hid from t102 where t101.hid=t102.hid);
HID HNAME
---------- ----------
1 name1
SQL> select * from t101 where exists (select hid from t102);
HID HNAME
---------- ----------
1 name1
2 name2
3 name3
SQL> select * from t101 where hid in (select hid from t102);
HID HNAME
---------- ----------
2 name2
3 name3
SQL> select * from t101 where not exists (select hid from t102);
no rows selected
SQL> select * from t101 where hid not in (select hid from t102);
HID HNAME
---------- ----------
1 name1
SQL> select * from t101 where exists (select hid from t102 where hid=2);
HID HNAME
---------- ----------
1 name1
2 name2
3 name3
SQL> select * from t101 where hid in (select hid from t102 where hid=2);
HID HNAME
---------- ----------
2 name2
SQL> select * from t101 where not exists (select hid from t102 where hid=2);
no rows selected
SQL> select * from t101 where hid not in (select hid from t102 where hid=2);
HID HNAME
---------- ----------
3 name3
1 name1
SQL> select * from t101 where exists (select hid from t102 where hid=8);
no rows selected
SQL> select * from t101 where hid in (select hid from t102 where hid=8);
no rows selected
SQL> select * from t101 where not exists (select hid from t102 where hid=8);
HID HNAME
---------- ----------
1 name1
2 name2
3 name3
SQL> select * from t101 where hid not in (select hid from t102 where hid=8);
HID HNAME
---------- ----------
3 name3
1 name1
2 name2
SQL> select * from t101 where exists (select 9 from dual where 1=1);
HID HNAME
---------- ----------
1 name1
2 name2
3 name3
SQL> select * from t101 where hid in (select 9 from dual where 1=1);
no rows selected
SQL> select * from t101 where not exists (select 9 from dual where 1=1);
no rows selected
SQL> select * from t101 where hid not in (select 9 from dual where 1=1);
HID HNAME
---------- ----------
1 name1
2 name2
3 name3
SQL> select * from t101 where exists (select 9 from dual where 1=2);
no rows selected
SQL> select * from t101 where hid in (select 9 from dual where 1=2);
no rows selected
SQL> select * from t101 where not exists (select 9 from dual where 1=2);
HID HNAME
---------- ----------
1 name1
2 name2
3 name3
SQL> select * from t101 where hid not in (select 9 from dual where 1=2);
HID HNAME
---------- ----------
1 name1
2 name2
3 name3
SQL> select * from t101 where exists (select null from dual);
HID HNAME
---------- ----------
1 name1
2 name2
3 name3
SQL> select * from t101 where hid in (select null from dual);
no rows selected
SQL> select * from t101 where not exists (select null from dual);
no rows selected
SQL> select * from t101 where hid not in (select null from dual);
no rows selected
SQL> select * from t101 where exists (select null from dual where 1=2);
no rows selected
SQL> select * from t101 where hid in (select null from dual where 1=2);
no rows selected
SQL> select * from t101 where not exists (select null from dual where 1=2);
HID HNAME
---------- ----------
1 name1
2 name2
3 name3
SQL> select * from t101 where hid not in (select null from dual where 1=2);
HID HNAME
---------- ----------
1 name1
2 name2
3 name3
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30126024/viewspace-2122360/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- sql中in和exists的原理及使用場景。SQL
- In和exists使用及效能分析(三)：in和exists的效能分析
- In和exists使用及效能分析(二)：exists的使用
- Oralce 使用SQL中的exists 和not exists 用法詳解SQL
- not in 和 not exists 比較和用法
- EXISTS、IN、NOT EXISTS、NOT IN(zt)
- Oracle中exists和in的效能差異Oracle
- SQL語句中exists和in的區別SQL
- mysql 關於exists 和in分析MySql
- In和exists使用及效能分析(一)：in的使用
- [20180808]exists and not exists.txt
- 前端路由原理解析和實現前端路由
- sql：delete if exists還是drop if exists?SQLdelete
- exists()、not exists() 、in()、not in()用法以及效率差異
- Laravel的unique和exists驗證規則的優化Laravel優化
- Volley的原理解析
- PTSQLServer中exists和except用法介紹wkaSQLServer
- [精選] SQL中的IN與NOT IN、EXISTS與NOT EXISTS的區別及效能分析SQL
- CSS的原理，如何解析？CSS
- KOA2框架原理解析和實現框架
- 視覺BEV基本原理和方案解析視覺
- LLM大模型：deepspeed實戰和原理解析大模型
- 深入淺出MyBatis：MyBatis解析和執行原理MyBatis
- Dubbo原理和原始碼解析之服務引用原始碼
- PHP 的 strtotime 的原理解析PHP
- 解析HOT原理
- DNS解析原理DNS
- 微服務架構的核心要點和實現原理解析微服務架構
- 插曲：Kafka的生產者案例和消費者原理解析Kafka
- Java 靜態代理和動態代理的使用及原理解析Java
- 【Mybatis原始碼解析】- JDBC連線資料庫的原理和操作MyBatis原始碼JDBC資料庫
- in、exists與索引索引
- MySQL慢查詢記錄原理和內容解析MySql
- Spring MVC原始碼(三) ----- @RequestBody和@ResponseBody原理解析SpringMVC原始碼
- DNS的原理和解析過程DNS
- Flutter之Key的原理解析Flutter
- 對線面試官：SQL中的IN與NOT IN、EXISTS與NOT EXISTS的區別及效能分析面試SQL
- Sentinel 原理-全解析
- Promise原理解析Promise