WHY？(https://blog.csdn.net/fly910905/article/details/78288685)
- IN 和 NOT IN 是比較常用的關(guān)鍵字，為什么要盡量避免呢？
1、效率低
項(xiàng)目中遇到這么個情況：
t1表 和 t2表 都是150w條數(shù)據(jù)，600M的樣子，都不算大。
-
但是這樣一句查詢
select * from t1 where phone not in (select phone from t2) 直接就把我跑傻了。。。十幾分鐘，檢查了一下 phone在兩個表都建了索引，字段類型也是一樣的。原來not in 是不能命中索引的。。。。
-
改成 NOT EXISTS 之后查詢 20s ，效率真的差好多。
select * from t1 where not EXISTS (select phone from t2 where t1.phone =t2.phone)
2、容易出現(xiàn)問題，或查詢結(jié)果有誤 （不能更嚴(yán)重的缺點(diǎn)）
-
以 IN 為例。建兩個表：test1 和 test2
create table test1 (id1 int)create table test2 (id2 int) insert into test1 (id1) values (1),(2),(3)insert into test2 (id2) values (1),(2) -
我想要查詢，在test2中存在的 test1中的id 。使用IN的一般寫法是：
select id1 from test1 where id1 in (select id2 from test2) -
結(jié)果是：image
OK 木有問題！
-
但是如果我一時手滑，寫成了：
select id1 from test1 where id1 in (select id1 from test2) 不小心把id2寫成id1了 ，會怎么樣呢?
-
結(jié)果是：image
EXCUSE ME！ 為什么不報錯？
單獨(dú)查詢 select id1 from test2 是一定會報錯: 消息 207，級別 16，狀態(tài) 1，第 11 行 列名 'id1' 無效。然而使用了IN的子查詢就是這么敷衍，直接查出 1 2 3 這僅僅是容易出錯的情況，自己不寫錯還沒啥事兒，下面來看一下 NOT IN 直接查出錯誤結(jié)果的情況：
給test2插入一個空值：
insert into test2 (id2) values (NULL)
-
我想要查詢，在test2中不存在的 test1中的id 。
select id1 from test1where id1 not in (select id2 from test2) -
結(jié)果是：image
空白！ 顯然這個結(jié)果不是我們想要的。我們想要3。為什么會這樣呢？
原因是：NULL不等于任何非空的值?。∪绻鹖d2只有1和2， 那么3<>1 且 3<>2 所以3輸出了，但是 id2包含空值，那么 3也不等于NULL 所以它不會輸出。
（跑題一句：建表的時候最好不要允許含空值，否則問題多多。）
HOW？
1、用 EXISTS 或 NOT EXISTS 代替
select * from test1where EXISTS (select * from test2 where id2 = id1 )select * FROM test1where NOT EXISTS (select * from test2 where id2 = id1 )
2、用JOIN 代替
select id1 from test1INNER JOIN test2 ON id2 = id1select id1 from test1LEFT JOIN test2 ON id2 = id1where id2 IS NULL
妥妥的沒有問題了！
- PS：那我們死活都不能用 IN 和 NOT IN 了么？并沒有，一位大神曾經(jīng)說過，如果是確定且有限的集合時，可以使用。如 IN （0，1，2）。
綜合以上對IN/EXISTS的討論，我們可以得出一個基本通用的結(jié)論：IN適合于外表大而內(nèi)表小的情況；EXISTS適合于外表小而內(nèi)表大的情況。
