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

来源：互联网发布：iphone控制mac 编辑：程序博客网时间：2024/06/06 16:43

简介摘要： 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