參考SQL查詢中in、exists、not in、not exists的用法與區(qū)別
1、in和exists
in是把外表和內(nèi)表作hash(字典集合)連接，而exists是對(duì)外表作循環(huán)，每次循環(huán)再對(duì)內(nèi)表進(jìn)行查詢。一直以來(lái)認(rèn)為exists比in效率高的說(shuō)法是不準(zhǔn)確的，如果查詢的兩個(gè)表大小相當(dāng)，那么用in和exists差別不大；如果兩個(gè)表中一個(gè)較小一個(gè)較大，則子查詢表大的用exists，子查詢表小的用in。
例如：表A(小表)，表B(大表)
遍歷使用
1）in()只執(zhí)行一次，它查出B表中的所有id字段并緩存起來(lái)。然后檢查A表的id是否與B表中的id相等，如果相等則將A表的記錄加入結(jié)果集中，直到遍歷完A表的所有記錄。
當(dāng)B表數(shù)據(jù)較大時(shí)不適合使用in()，因?yàn)樗鼤?huì)把B表數(shù)據(jù)全部遍歷一次。
如：A表有10000條記錄，B表有1000000條記錄，那么最多有可能遍歷10000*1000000次，效率很差。
如：A表有10000條記錄，B表有100條記錄，那么最多有可能遍歷10000*100次，遍歷次數(shù)大大減少，效率大大提升。
結(jié)論：in()適合B表比A表數(shù)據(jù)小的情況
2）exists()會(huì)執(zhí)行A.length次，它并不緩存exists()結(jié)果集，因?yàn)閑xists()結(jié)果集的內(nèi)容并不重要，重要的是其內(nèi)查詢語(yǔ)句的結(jié)果集空或者非空，空則返回false，非空則返回true。
它的查詢過(guò)程類似于以下代碼的執(zhí)行過(guò)程：
List resultSet={};
Array A=(select * from A);
for(int i=0;i<A.length;i++) {
if(exists(A[i].id) {? //執(zhí)行select id from B where B.id=A.id是否有記錄返回
resultSet.add(A[i]);
}
}
return resultSet;
當(dāng)B表比A表數(shù)據(jù)大時(shí)適合使用exists()，因?yàn)樗鼪](méi)有那么多遍歷操作，只需要再執(zhí)行一次查詢就行。
如：A表有10000條記錄，B表有1000000條記錄，那么exists()會(huì)執(zhí)行10000次去判斷A表中的id是否與B表中的id相等。
如：A表有10000條記錄，B表有100000000條記錄，那么exists()還是執(zhí)行10000次，因?yàn)樗粓?zhí)行A.length次，可見(jiàn)B表數(shù)據(jù)越多，越適合exists()發(fā)揮效果。
再如：A表有10000條記錄，B表有100條記錄，那么exists()還是執(zhí)行10000次，還不如使用in()遍歷10000*100次，因?yàn)閕n()是在內(nèi)存里遍歷比較，而exists()需要查詢數(shù)據(jù)庫(kù)，我們都知道查詢數(shù)據(jù)庫(kù)所消耗的性能更高，而內(nèi)存比較很快。
結(jié)論：exists()適合B表比A表數(shù)據(jù)大的情況。
當(dāng)A表數(shù)據(jù)與B表數(shù)據(jù)一樣大時(shí)，in與exists效率差不多，可任選一個(gè)使用。
2、not in 和not exists
not in 邏輯上不完全等同于not exists，如果你誤用了not in，小心你的程序存在致命的bug。
請(qǐng)看下面的例子：
create table A1 (c1 int,c2 int);
create table A2 (c1 int,c2 int);
insert into A1 values(1,2);
insert into A1 values(1,3);
insert into A2 values(1,2);
insert into A2 values(1,null);
select * from A1 where c2 not in(select c2 from A2);? ? ? ? ? ? ? ? ? ? ? ? ? ? -->執(zhí)行結(jié)果：無(wú)（null）
select * from A1 where not exists(select c2 from A2 where A2.c2=A1.c2);? ? -->執(zhí)行結(jié)果：1 3
正如所看到的，not in出現(xiàn)了不期望的結(jié)果集，存在邏輯錯(cuò)誤。使用not in(它會(huì)調(diào)用子查詢)，而使用not exists（它會(huì)調(diào)用關(guān)聯(lián)子查詢）。如果子查詢中返回的任意一條記錄含有空值，則查詢將不返回任何記錄。如果子查詢字段有非空限制，這時(shí)可以使用not in。
如果查詢語(yǔ)句使用了not in，那么對(duì)內(nèi)外表都進(jìn)行全表掃描，沒(méi)有用到索引；而not exists的子查詢依然能用到表上的索引。所以無(wú)論哪個(gè)表大，用not exists都比not in 要快。
3、in 和 =
select name from employee where name in('張三','李四','王五');
與
select name from employee where name='張三' or name='李四' or name='王五';
的結(jié)果是相同的。
4.exists防止插入重復(fù)記錄
有時(shí)需要插入非重復(fù)記錄，在Mysql中可以使用ignore關(guān)鍵字來(lái)忽略已有記錄，但是其只能通過(guò)主鍵忽略，不能根據(jù)自定義條件忽略。
其語(yǔ)法為：insert ignore into tableName (column1,column2,……) values (value1,value2,……);
但是其他數(shù)據(jù)庫(kù)不一定提供類似ignore關(guān)鍵字，所以可以使用exists條件句防止插入重復(fù)記錄。
insert into A (name,age) select name,age from B where not exists (select 1 from A where A.id=B.id);
5.關(guān)于exists更多說(shuō)明
exists用于檢查子查詢返回的結(jié)果集是否為空，該子查詢實(shí)際上并不返回任何數(shù)據(jù)，而是返回值true或false。
語(yǔ)法： exists subQuery
參數(shù)： subQuery 是一個(gè)受限的 select 語(yǔ)句 (不允許有 compute 子句和 into 關(guān)鍵字)。
結(jié)果類型： boolean 如果子查詢包含行，則返回 true ，否則返回 false 。
結(jié)論：select * from A where exists (select 1 from B where A.id=B.id);
一種通俗的可以理解為：將外查詢表的每一行，代入內(nèi)查詢作為檢驗(yàn)，如果內(nèi)查詢返回的結(jié)果集非空，則exists子句返回true，這一行方可作為外查詢的結(jié)果行，否則不能作為結(jié)果。
