SQL中IN,NOT IN,EXISTS,NOT EXISTS的用法和差別:

IN:確定給定的值是否與子查詢或列表中的值相匹配。
IN 關(guān)鍵字使您得以選擇與列表中的任意一個(gè)值匹配的行。
當(dāng)要獲得居住在 California、Indiana 或 Maryland 州的所有作者的姓名和州的列表時(shí)，就需要下列查詢：
SELECT ProductID, ProductName FROM Northwind.dbo.Products WHERE CategoryID = 1 OR CategoryID = 4 OR CategoryID = 5
然而，如果使用 IN，少鍵入一些字符也可以得到同樣的結(jié)果：
SELECT ProductID, ProductName FROM Northwind.dbo.Products WHERE CategoryID IN (1, 4, 5)
IN 關(guān)鍵字之后的項(xiàng)目必須用逗號(hào)隔開，并且括在括號(hào)中。
下列查詢?cè)?titleauthor 表中查找在任一種書中得到的版稅少于 50% 的所有作者的 au_id，然后從 authors 表中選擇 au_id 與

titleauthor 查詢結(jié)果匹配的所有作者的姓名：
SELECT au_lname, au_fname FROM authors WHERE au_id IN (SELECT au_id FROM titleauthor WHERE royaltyper < 50)
結(jié)果顯示有一些作者屬于少于 50% 的一類。

NOT IN:通過 NOT IN 關(guān)鍵字引入的子查詢也返回一列零值或更多值。
以下查詢查找沒有出版過商業(yè)書籍的出版商的名稱。
SELECT pub_name FROM publishers WHERE pub_id NOT IN (SELECT pub_id FROM titles WHERE type = 'business')

使用 EXISTS 和 NOT EXISTS 引入的子查詢可用于兩種集合原理的操作：交集與差集。兩個(gè)集合的交集包含同時(shí)屬于兩個(gè)原集合的所有元素。

差集包含只屬于兩個(gè)集合中的第一個(gè)集合的元素。

EXISTS:指定一個(gè)子查詢，檢測(cè)行的存在。
本示例所示查詢查找由位于以字母 B 開頭的城市中的任一出版商出版的書名：
SELECT DISTINCT pub_name FROM publishers WHERE EXISTS (SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type =

'business')
SELECT distinct pub_name FROM publishers WHERE pub_id IN (SELECT pub_id FROM titles WHERE type = 'business')
兩者的區(qū)別:
EXISTS:后面可以是整句的查詢語句如:SELECT * FROM titles
IN:后面只能是對(duì)單列:SELECT pub_id FROM titles

NOT EXISTS:
例如，要查找不出版商業(yè)書籍的出版商的名稱：
SELECT pub_name FROM publishers WHERE NOT EXISTS (SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type =

'business')
下面的查詢查找已經(jīng)不銷售的書的名稱:
SELECT title FROM titles WHERE NOT EXISTS (SELECT title_id FROM sales WHERE title_id = titles.title_id)

本文來自CSDN博客，轉(zhuǎn)載請(qǐng)標(biāo)明出處： http://blog.csdn.net/feixiang7443/archive/2010/04/21/5510012.aspx

?

?

另外，到底什么時(shí)候用IN? , 什么時(shí)候用? EXISTS?

?

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

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

in 是把外表和內(nèi)表作hash 連接，而exists是對(duì)外表作loop循環(huán)，每次loop循環(huán)再對(duì)內(nèi)表進(jìn)行查詢。
如果查詢的兩個(gè)表大小相當(dāng)，那么用in和exists差別不大。
如果兩個(gè)表中一個(gè)較小，一個(gè)是大表， 則子查詢表大的用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 那么內(nèi)外表都進(jìn)行全表掃描，沒有用到索引；而not extsts 的子查詢依然能用到表上的索引。所以無論那個(gè)表大，用not exists都比not in要快。

3.exist與in都可以實(shí)現(xiàn)一個(gè)目的.二者都可以用來過濾數(shù)據(jù).

示例:

    

      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,因?yàn)閑xist只會(huì)利用子表t2的復(fù)合索引keyid+ideaid,而子表內(nèi)容要小與主表,主表由于無法使用索引,查詢效率低下.
      

      

      

      

      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,因?yàn)閕n只會(huì)使用主表t1里面的復(fù)合主鍵keyid-ideaid,在主表大于子表的情況下,會(huì)很好的利用主表的索引.
      

      

      

      --后二條sql的執(zhí)行結(jié)果都是一樣的.說明exist與in在用法上可以達(dá)到一個(gè)目的,不同的地方是
      

      --1.性能的考慮此時(shí)就按子表大主表小用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 條件時(shí)，是先對(duì)where 前的主查詢?cè)冞M(jìn)行查詢，然后用主查詢的結(jié)果一個(gè)一個(gè)的代入exists的查詢進(jìn)行判斷，如果為真則輸出當(dāng)前這一條主查詢的結(jié)果，否則不輸出

比如

如下：
表A
ID NAME
1 A1
2 A2
3 A3
表B
ID AID NAME
1 1 ?? B1
2 2 ?? B2
3 2 ?? B3
表A和表B是一對(duì)多的關(guān)系 A.ID --> B.AID
SELECT ID , NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE A.ID = B.AID)
執(zhí)行結(jié)果為
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有值返回真所以有數(shù)據(jù)
SELECT ID , NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID = 2 )
-->SELECT * FROM B WHERE B.AID = 2有值返回真所以有數(shù)據(jù)
SELECT ID , NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID = 3 )
-->SELECT * FROM B WHERE B.AID = 3無值返回真所以沒有數(shù)據(jù)
NOT EXISTS 就是反過來
SELECT ID , NAME FROM A WHERE NOT EXIST (SELECT * FROM B WHERE A.ID = B.AID)
執(zhí)行結(jié)果為
3 A3

5. in 與 =的區(qū)別
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'
的結(jié)果是相同的。

in的字段也可以與其它字段建復(fù)合索引.

比如

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建成復(fù)合索引.
    
  

SQL中IN,NOT IN,EXISTS,NOT EXISTS的用法和差別