SQL中IN和EXISTS用法的區別

1.exist,not exist一般都是與子查詢一起使用. In可以與子查詢一起使用,也可以直接in (a,b.....)

2.exist會針對子查詢的表使用索引. not exist會對主子查詢都會使用索引. in與子查詢一起使用的時候,只能針對主查詢使用索引. not in則不會使用任何索引. 注意,一直以來認為exists比in效率高的說法是不準確的。

in 是把外表和內表作hash 串連，而exists是對外表作loop迴圈，每次loop迴圈再對內表進行查詢。
如果查詢的兩個表大小相當，那麼用in和exists差別不大。
如果兩個表中一個較小，一個是大表，則子查詢表大的用exists，子查詢表小的用in：
例如：表A（小表），表B（大表）1：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列的索引。
相反的2：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列的索引。
not in 和not
exists如果查詢語句使用了not in 那麼內外表都進行全表掃描，沒有用到索引；而not extsts 的子查詢依然能用到表上的索引。所以無論那個表大，用not exists都比not
in要快。

3.exist與in都可以實現一個目的.二者都可以用來過濾資料.

樣本:

select count(1) from t1;--160W
select count(1) from t2; --90W


SELECT count(1)
FROM t1 a
WHERE EXISTS (SELECT accountid
FROM t2 b
WHERE a.keyid = b.keyid AND a.ideaid = b.ideaid);--主大子小,不適合使用exist,因為exist只會利用子表t2的複合索引keyid+ideaid,而子表內容要小與主表,主表由於無法使用索引,查詢效率低下.



select count(1) from t1 a where accountid in (SELECT accountid
FROM t2 b
WHERE a.keyid = b.keyid AND a.ideaid = b.ideaid);--主大子小,適合用in,因為in只會使用主表t1裡面的複合主鍵keyid-ideaid,在主表大於子表的情況下,會很好的利用主表的索引.


--後二條sql的執行結果都是一樣的.說明exist與in在用法上可以達到一個目的,不同的地方是
--1.效能的考慮此時就按子表大主表小用exist,子表小主表大用in的原則就可以.
--2.寫法的不同, exist的where條件是:      "......  where exist (..... where a.id=b.id)"
--in的where條件是: " ...... where  id in ( select id .... where a.id=b.id)"

4. exist的原理:

exists做為where 條件時，是先對where 前的主查詢詢進行查詢，然後用主查詢的結果一個一個的代入exists的查詢進行判斷，如果為真則輸出當前這一條主查詢的結果，否則不輸出

比如

如下：
表A
ID NAME
1 A1
2 A2
3 A3

表B
ID AID NAME
1 1   B1
2 2   B2
3 2   B3

表A和表B是一對多的關係 A.ID --> B.AID

SELECT ID
, NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE
A.ID =
B.AID)
執行結果為
1 A1
2 A2
原因可以按照如下分析
SELECT ID
, NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE
B.AID = 1)
-->SELECT * FROM B WHERE B.AID = 1有值返回真所以有資料

SELECT ID
, NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE
B.AID = 2)
-->SELECT * FROM B WHERE B.AID = 2有值返回真所以有資料

SELECT ID
, NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE
B.AID = 3)
-->SELECT * FROM B WHERE B.AID = 3無值返回真所以沒有資料

NOT EXISTS 就是反過來
SELECT ID
, NAME FROM A WHERE NOT EXIST (SELECT * FROM B WHERE A.ID =
B.AID)
執行結果為
3 A3

5. in 與 =的區別
select name from student where name
in ('zhang','wang','li','zhao');
與
select name from student where name='zhang' or name='li' or name='wang' or name='zhao'
的結果是相同的。

in的欄位也可以與其它欄位建複合索引.

比如

T1包含下面key, accountd,groupid.

SELECT   *
FROM   T1  a
WHERE       a.groupid = 2001
AND a.accountid = 1001
AND a.key IN ('abc', 'def', 'ala');

--上面的sql可以將accountid,key建成複合索引.