【分享】sql中 in 、not in 、exists、not exists 用法和差別

andymark

sql中 in 、not in 、exists、not exists 用法和差別

exists （sql 返回結(jié)果集為真）
not exists (sql 不返回結(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 EXIST (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=１)
--->SELECT * FROM B WHERE B.AID=１有值返回真所以有數(shù)據(jù)

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2)
--->SELECT * FROM B WHERE B.AID=２有值返回真所以有數(shù)據(jù)

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3)
--->SELECT * FROM B WHERE B.AID=３無值返回真所以沒有數(shù)據(jù)

NOT EXISTS 就是反過來
SELECT ID,NAME FROM A WHERE　NOT EXIST (SELECT * FROM B WHERE A.ID=B.AID)
執(zhí)行結(jié)果為
3 A3
===========================================================================
EXISTS = IN,意思相同不過語法上有點(diǎn)點(diǎn)區(qū)別，好像使用IN效率要差點(diǎn)，應(yīng)該是不會(huì)執(zhí)行索引的原因
SELECT ID,NAME FROM A　 WHERE　ID IN (SELECT AID FROM B)

NOT EXISTS = NOT IN ,意思相同不過語法上有點(diǎn)點(diǎn)區(qū)別
SELECT ID,NAME FROM A WHERE　ID　NOT IN (SELECT AID FROM B)


下面是普通的用法：

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)

語法

EXISTS subquery
參數(shù)
subquery:是一個(gè)受限的 SELECT 語句 (不允許有 COMPUTE 子句和 INTO 關(guān)鍵字)。有關(guān)更多信息，請(qǐng)參見 SELECT 中有關(guān)子查詢的討論。

結(jié)果類型:Boolean


結(jié)果值:如果子查詢包含行，則返回 TRUE。


示例
A. 在子查詢中使用 NULL 仍然返回結(jié)果集

這個(gè)例子在子查詢中指定 NULL，并返回結(jié)果集，通過使用 EXISTS 仍取值為 TRUE。

SELECT CategoryName
FROM Categories
WHERE EXISTS (SELECT NULL)
ORDER BY CategoryName ASC

B. 比較使用 EXISTS 和 IN 的查詢

這個(gè)例子比較了兩個(gè)語義類似的查詢。第一個(gè)查詢使用 EXISTS 而第二個(gè)查詢使用 IN。注意兩個(gè)查詢返回相同的信息。


SELECT DISTINCT pub_name
FROM publishers
WHERE EXISTS
    (SELECT *
    FROM titles
    WHERE pub_id = publishers.pub_id
    AND type = \'business\')

-- Or, using the IN clause:


SELECT distinct pub_name
FROM publishers
WHERE pub_id IN
    (SELECT pub_id
    FROM titles
    WHERE type = \'business\')


下面是任一查詢的結(jié)果集：

pub_name                                
----------------------------------------
Algodata Infosystems                    
New Moon Books                          

C.比較使用 EXISTS 和 = ANY 的查詢

本示例顯示查找與出版商住在同一城市中的作者的兩種查詢方法：第一種方法使用 = ANY，第二種方法使用 EXISTS。注意這兩種方法返回相同的信息。

SELECT au_lname, au_fname
FROM authors
WHERE exists
    (SELECT *
    FROM publishers
    WHERE authors.city = publishers.city)

-- Or, using = ANY

SELECT au_lname, au_fname
FROM authors
WHERE city = ANY
    (SELECT city
    FROM publishers)

D.比較使用 EXISTS 和 IN 的查詢

本示例所示查詢查找由位于以字母 B 開頭的城市中的任一出版商出版的書名：

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

-- Or, using IN:

SELECT title
FROM titles
WHERE pub_id IN
    (SELECT pub_id
    FROM publishers
    WHERE city LIKE \'B%\')

E. 使用 NOT EXISTS

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

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

魚兒游游

andymark,我頂你，你是好樣的，學(xué)習(xí)了，多謝。

t小寶

開心今天又大甩賣了

輕風(fēng)

留個(gè)記號(hào)以慢慢看

wang1950317

慢慢學(xué)習(xí)！

purplerose

雖然是基礎(chǔ)的東西，但常常人們不知如何去應(yīng)用，不錯(cuò)，贊一個(gè)

purplerose

雖然是基礎(chǔ)的東西，但常常人們不知如何去應(yīng)用，不錯(cuò)，贊一個(gè)

purplerose

雖然是基礎(chǔ)的東西，但常常人們不知如何去應(yīng)用，不錯(cuò)，贊一個(gè)