SQL中的in和exists的區別

在網上看到一個很好的例子講解in和exists的區別，這裡備忘下。

本樣本所示查詢尋找由位於以字母 B 開頭的城市中的任一出版商出版的書名：
USE pubs

SELECT title
FROM titles WHERE EXISTS
    (SELECT *
    FROM publishers
    WHERE pub_id = titles.pub_id
    AND city LIKE  'B%')
GO

-- Or, using IN:

USE pubs
GO
SELECT title
FROM titles
WHERE pub_id IN
    (SELECT pub_id
    FROM publishers
    WHERE city LIKE  'B%')
GO 

使用 NOT EXISTS

NOT EXISTS 的作用與 EXISTS 正相反。如果子查詢沒有返回行，則滿足 NOT EXISTS 中的 WHERE 子句。本樣本尋找不出版商業書籍的出版商的名稱：

USE pubs
GO
SELECT pub_name
FROM publishers
WHERE NOT EXISTS
    (SELECT *
    FROM titles
    WHERE pub_id = publishers.pub_id
    AND type = 'business')
ORDER BY pub_name
GO

 

 

in和exists
in 是把外表和內表作hash 串連，而exists是對外表作loop迴圈，每次loop迴圈再對內表進行查詢。一直以來認為exists比in效率高的說法是不準確的。
如果查詢的兩個表大小相當，那麼用in和exists差別不大。
如果兩個表中一個較小，一個是大表，則子查詢表大的用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列的索引。
相反的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要快。
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和exists的SQL執行效率分析

　　A，B兩個表，

　　(1)當只顯示一個表的資料如A，關聯準則只一個如ID時，使用IN更快：

　　select * from A where id in (select id from B)

　　(2)當只顯示一個表的資料如A，關聯準則不只一個如ID，col1時，使用IN就不方便了，可以使用EXISTS：

　　select * from A

　　where exists (select 1 from B where id = A.id and col1 = A.col1)

　　(3)當只顯示兩個表的資料時，使用IN，EXISTS都不合適，要使用串連：

　　select * from A left join B on id = A.id

　　所以使用何種方式，要根據要求來定。