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

     簡介摘要： exists （sql 返回結果集為真） not exists (sql 不返回結果集為真） 如下： 表A ID NAME 1 A1 2 A2

 exists   （sql 返回結果集為真）
  not     exists   (sql 不返回結果集為真）
如下：
表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   EXIST (  SELECT     *     FROM   B   WHERE   A.ID  =  B.AID)
執行[zhi hang]結果為
  1   A1
  2   A2
原因可以按照如下分析
  SELECT   ID,NAME   FROM   A   WHERE     EXISTS   (  SELECT     *     FROM   B   WHERE   B.AID  =  １  )
  --  ->SELECT * FROM B WHERE B.AID=１有值返回真所以有資料[shu ju]   
  
  SELECT   ID,NAME   FROM   A   WHERE     EXISTS   (  SELECT     *     FROM   B   WHERE   B.AID  =  2  )
  --  ->SELECT * FROM B WHERE B.AID=２有值返回真所以有資料[shu ju]   
  
  SELECT   ID,NAME   FROM   A   WHERE     EXISTS   (  SELECT     *     FROM   B   WHERE   B.AID  =  3  )
  --  ->SELECT * FROM B WHERE B.AID=３無值返回真所以沒有資料[shu ju]   
  
  NOT     EXISTS   就是反過來
  SELECT   ID,NAME   FROM   A   WHERE  　  NOT   EXIST (  SELECT     *     FROM   B   WHERE   A.ID  =  B.AID)
執行[zhi hang]結果為
  3   A3
  ===========================================================================  
  EXISTS     =     IN  ,意思相同不過文法[yu fa]上有點點區別，好像使用IN效率要差點，應該是不會執行[zhi hang]索引[suo yin]的原因
  SELECT   ID,NAME   FROM   A　   WHERE  　ID   IN   (  SELECT   AID   FROM   B)

  NOT     EXISTS     =     NOT     IN   ,意思相同不過文法[yu fa]上有點點區別
  SELECT   ID,NAME   FROM   A   WHERE  　ID　  NOT     IN   (  SELECT   AID   FROM   B)

下面是普通的用法：

SQL中IN,  NOT     IN  ,  EXISTS  ,  NOT   EXISTS的用法和差別:
　　  IN  :確定給定的值是否與子查詢或列表[lie biao]中的值相匹配。
　　  IN   關鍵字[guan jian zi]使您得以選擇[xuan ze]與列表[lie biao]中的任意一個值匹配的行。
　　當要獲得居住在 California、Indiana 或 Maryland 州的所有作者的姓名和州的列表[lie biao]時，就需要下列查詢：
　　  SELECT   ProductID, ProductName   FROM   Northwind.dbo.Products   WHERE   CategoryID   =     1     OR   CategoryID   =     4     OR   CategoryID   =     5  
　　然而，如果使用   IN  ，少鍵入一些字元[zi fu]也可以得到同樣的結果：
　　  SELECT   ProductID, ProductName   FROM   Northwind.dbo.Products   WHERE   CategoryID   IN   (  1  ,   4  ,   5  )
　　  IN   關鍵字[guan jian zi]之後的項目[xiang mu]必須用逗號隔開，並且括在括弧中。
　　下列查詢在 titleauthor 表中尋找[cha zhao]在任一種書中得到的版稅少於   50  %   的所有作者的 au_id，然後從 authors 表中選擇[xuan ze] au_id 與
　　titleauthor 查詢結果匹配的所有作者的姓名：
　　  SELECT   au_lname, au_fname   FROM   authors   WHERE   au_id   IN   (  SELECT   au_id   FROM   titleauthor   WHERE   royaltyper   <  50  )
　　結果顯示[xian shi]有一些作者屬於少於   50  %   的一類。
　　  NOT     IN  :通過   NOT     IN   關鍵字[guan jian zi]引入的子查詢也返回一列零值或更多值。
　　以下查詢尋找[cha zhao]沒有出版過商業書籍的出版商的名稱。
　　  SELECT   pub_name   FROM   publishers   WHERE   pub_id   NOT     IN   (  SELECT   pub_id   FROM   titles   WHERE   type   =     '  business  '  )
　　使用   EXISTS   和   NOT     EXISTS   引入的子查詢可用於[yong yu]兩種集合原理的操作：交集與差集。
      兩個集合的交集包含同時屬於兩個原集合的所有元素。
　　差集包含只屬於兩個集合中的第一個集合的元素。
　　  EXISTS  :指定一個子查詢，檢測行的存在。
　　本樣本所示查詢尋找[cha zhao]由位於以字母 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  '  )
　　兩者的區別:
　　  EXISTS  :後面可以是整句的查詢語句[yu ju]如:  SELECT     *     FROM   titles
　　  IN  :後面只能是對單列:  SELECT   pub_id   FROM   titles
　　  NOT     EXISTS  :
　　例如，要尋找[cha zhao]不出版商業書籍的出版商的名稱：
　　  SELECT   pub_name   FROM   publishers   WHERE     NOT     EXISTS   (  SELECT     *     FROM   titles   WHERE   pub_id   =   publishers.pub_id   AND   type   =  
　　  '  business  '  )
　　下面的查詢尋找[cha zhao]已經不銷售的書的名稱:
　　  SELECT   title   FROM   titles   WHERE     NOT     EXISTS   (  SELECT   title_id   FROM   sales   WHERE   title_id   =   titles.title_id)

文法[yu fa]

  EXISTS   subquery
參數[can shu]
subquery:是一個受限的   SELECT   語句[yu ju] (不允許有   COMPUTE   子句[zi ju]和   INTO   關鍵字[guan jian zi])。有關更多資訊[xin xi]，請參見   SELECT   中有關子查詢的討論。

結果類型[lei xing]:Boolean

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

樣本
A. 在子查詢中使用   NULL   仍然返回結果集

這個例子在子查詢中指定   NULL  ，並返回結果集，通過使用   EXISTS   仍取值為 TRUE。

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

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

這個例子比較了兩個語義[yu yi]類似的查詢。第一個查詢使用   EXISTS   而第二個查詢使用   IN  。注意兩個查詢返回相同的資訊[xin xi]。

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

  --   Or, using the IN clause:   
  
  USE   pubs
  GO  
  SELECT     distinct   pub_name
  FROM   publishers
  WHERE   pub_id   IN  
    (  SELECT   pub_id
      FROM   titles
      WHERE   type   =   /  '  business/  '  )
  GO  

下面是任一查詢的結果集：

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

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

本樣本顯示[xian shi]尋找[cha zhao]與出版商住在同一城市中的作者的兩種查詢方法[fang fa]：第一種方法[fang fa]使用   =     ANY  ，第二種方法[fang fa]使用   EXISTS  。注意這兩種方法[fang fa]返回相同的資訊[xin xi]。

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

  --   Or, using = ANY   
  
  USE   pubs
  GO  
  SELECT   au_lname, au_fname
  FROM   authors
  WHERE   city   =     ANY  
    (  SELECT   city
      FROM   publishers)
  GO  

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

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

  USE   pubs
  GO  
  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  

E. 使用   NOT     EXISTS  

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

  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