关于Oracle中in,exists 与 not in, not exists
?文章簡要的討論了in,exists 與 not in, not exists在使用中的問題，主要是關(guān)鍵字的選擇，SQL的優(yōu)化
*注：下面示例都是用Oracle內(nèi)置用戶的表，如果安裝Oracle時(shí)沒有選擇不安裝數(shù)據(jù)庫示例表應(yīng)該都會安裝的
1、IN和EXISTS
IN語句：
SELECT * FROM hr.employees t1 WHERE t1.employee_id IN (SELECT t2.employee_idFROM hr.job_history t2 );EXISTS語句：
SELECT * FROM hr.employees t1 WHERE EXISTS (SELECT 1FROM hr.job_history t2WHERE t2.employee_id = t1.employee_id );? 可以看到兩者的結(jié)果是一樣的，這意味著兩個(gè)查詢都能夠滿足我們業(yè)務(wù)的需求。但是問題來了，那個(gè)以查詢更快呢？
? 用in和exists都可以實(shí)現(xiàn)對數(shù)據(jù)的選擇，但是兩者的效率往往會因?yàn)閳鼍安煌煌?/strong>。原因如下：
in是把主表和子查詢的表作hash連接；而exists是對主表作loop循環(huán)，每次loop循環(huán)再對內(nèi)表進(jìn)行查詢。所以我們一直以來認(rèn)為exists比in效率高的說法是不準(zhǔn)確的。如果查詢的兩個(gè)表大小相當(dāng)，那么用in和exists差別不大；如果兩個(gè)表中一個(gè)較小一個(gè)較大，則子查詢表大的用exists，子查詢表小的用in；
即：表A(小表)，表B(大表)
select * from A where cc in(select cc from B) -->效率低，用到了A表上cc列的索引；select * from A where exists(select cc from B where cc=A.cc) -->效率高，用到了B表上cc列的索引。相反的：
select * from B where cc in(select cc from A) -->效率高，用到了B表上cc列的索引select * from B where exists(select cc from A where cc=B.cc) -->效率低，用到了A表上cc列的索引。?
2、NOT?IN和NOT EXISTS
NOT IN語句：
SELECT * FROM HR.EMPLOYEES T1 WHERE T1.EMPLOYEE_ID NOT IN (SELECT T2.EMPLOYEE_ID FROM HR.JOB_HISTORY T2);NOT EXISTS語句：
SELECT * FROM HR.EMPLOYEES T1 WHERE NOT EXISTS (SELECT 1FROM HR.JOB_HISTORY T2WHERE T2.EMPLOYEE_ID = T1.EMPLOYEE_ID);? not in,not exists的對比與in,exists有比較大的不同，原因在于：
? 如果查詢語句使用了not in，那么對主表，子查詢表都進(jìn)行全表掃描，沒有用到索引；而not exists的子查詢依然能用到表上的索引。所以無論哪個(gè)表大，用not exists都比not in 要快。
? 而且坑爹的事情還沒有這么快就結(jié)束！
再演示一個(gè)比較坑爹的事情
--構(gòu)造臨時(shí)表tmp1 WITH tmp1 AS ( SELECT 1 AS field1,2 AS field2 FROM dual UNION ALL SELECT 1 AS field1,3 AS field2 FROM dual ),--多個(gè)with as用逗號隔開 --構(gòu)造臨時(shí)表tmp2 tmp2 AS ( SELECT 1 AS field1,2 AS field2 FROM dual UNION ALL SELECT 1 AS field1,NULL AS field2 FROM dual ) SELECT * FROM tmp1 t1 WHERE NOT EXISTS (SELECT 1FROM tmp2 t2WHERE t1.field2 = t2.field2);結(jié)果如下：
沒有什么異常，但是用 not in的話坑爹的事情就會出現(xiàn)了！
--構(gòu)造臨時(shí)表tmp1 WITH tmp1 AS ( SELECT 1 AS field1,2 AS field2 FROM dual UNION ALL SELECT 1 AS field1,3 AS field2 FROM dual ),--多個(gè)with as用逗號隔開 --構(gòu)造臨時(shí)表tmp2 tmp2 AS ( SELECT 1 AS field1,2 AS field2 FROM dual UNION ALL SELECT 1 AS field1,NULL AS field2 FROM dual ) SELECT * FROM tmp1 t1 WHERE t1.field2 NOT IN (SELECT t2.field2FROM tmp2 t2);結(jié)果如下：
WTF!!!!!!!
為什么會不同？？？？？？
原來使用not in時(shí)，它會調(diào)用子查詢；而使用not exists時(shí)，它會調(diào)用關(guān)聯(lián)子查詢。如果子查詢中返回的任意一條記錄含有空值，則查詢將不返回任何記錄。這就是導(dǎo)致我們上述問題的原因，所以一般情況下，我們都會用not exists而不用not in
轉(zhuǎn)載于:https://www.cnblogs.com/zhongjiajie/p/5652047.html
總結(jié)
以上是生活随笔為你收集整理的关于Oracle中in,exists 与 not in, not exists的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 如何将四个一字节的数转换为一个四字节数
- 下一篇： 拉力赛 (Standard IO)