[转]sql中 in 、not in 、exists、not exists 用法和差别

转载：http://blog.csdn.net/lihan6415151528/archive/2009/08/20/4466914.aspx

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)
执行结果为
1 A1
2 A2
原因可以按照如下分析
SELECT ID,NAME FROM A WHERE EXISTS ( SELECT * FROM B WHERE B.AID = １ )
-- ->SELECT * FROM B WHERE B.AID=１有值返回真所以有数据

SELECT ID,NAME FROM A WHERE EXISTS ( SELECT * FROM B WHERE B.AID = 2 )
-- ->SELECT * FROM B WHERE B.AID=２有值返回真所以有数据

SELECT ID,NAME FROM A WHERE EXISTS ( SELECT * FROM B WHERE B.AID = 3 )
-- ->SELECT * FROM B WHERE B.AID=３无值返回真所以没有数据

NOT EXISTS 就是反过来
SELECT ID,NAME FROM A WHERE 　 NOT EXIST ( SELECT * FROM B WHERE A.ID = B.AID)
执行结果为
3 A3
===========================================================================
EXISTS = IN ,意思相同不过语法上有点点区别，好像使用IN效率要差点，应该是不会执行索引的原因
SELECT ID,NAME FROM A　 WHERE 　ID IN ( SELECT AID FROM B)

NOT EXISTS = NOT IN ,意思相同不过语法上有点点区别
SELECT ID,NAME FROM A WHERE 　ID　 NOT IN ( SELECT AID FROM B)


下面是普通的用法：

SQL中IN, NOT IN , EXISTS , NOT EXISTS的用法和差别:
　　 IN :确定给定的值是否与子查询或列表中的值相匹 配。
　　 IN 关键字使您得以选择与列表中的任意一个值匹配的行。
　　当要获得居住在 California、Indiana 或 Maryland 州的所有作者的姓名和州的列表时，就需要下列查询：
　　 SELECT ProductID, ProductName FROM Northwind.dbo.Products WHERE CategoryID = 1 OR CategoryID = 4 OR CategoryID = 5
　　然而，如果使用 IN ，少键入一些字 符也可以得到同样的结果：
　　 SELECT ProductID, ProductName FROM Northwind.dbo.Products WHERE CategoryID IN ( 1 , 4 , 5 )
　　 IN 关键字之后的项目必须用逗号隔开，并且括在括号中。
　　下列查询在 titleauthor 表中查找在任一种书中得到的版税少于 50 % 的所有作者的 au_id，然后从 authors 表中选择 au_id 与
　　titleauthor 查询结果匹配的所有作者的姓名：
　　 SELECT au_lname, au_fname FROM authors WHERE au_id IN ( SELECT au_id FROM titleauthor WHERE royaltyper < 50 )
　　结果显示有一些作者属于少于 50 % 的一类。
　　 NOT IN :通过 NOT IN 关键字引入的子查询也返回一列零值或更多值。
　　以下查询查找没有出版过商业书 籍的出版商的名称。
　　 SELECT pub_name FROM publishers WHERE pub_id NOT IN ( SELECT pub_id FROM titles WHERE type = ' business ' )
　　使用 EXISTS 和 NOT EXISTS 引入的子查询可用于两种集合原理的操作：交集与差集。
      两个集合的交集包含同时属于两个原集合的所有元素。
　　差集包含只属于两个集合中的第一个集合的元素。
　　 EXISTS :指定一个子查询，检测行的存在。
　　本示例所示查询查找由位于以字母 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 :后面可以是整句的查询语句如: SELECT * FROM titles
　　 IN :后面只能是对 单列: SELECT pub_id FROM titles
　　 NOT EXISTS :
　　例如，要查找不出版商业书籍的出版商的名称：
　　 SELECT pub_name FROM publishers WHERE NOT EXISTS ( SELECT * FROM titles WHERE pub_id = publishers.pub_id AND type =
　　 ' business ' )
　　下面的查 询查找已经不销售的书的名称:
　　 SELECT title FROM titles WHERE NOT EXISTS ( SELECT title_id FROM sales WHERE title_id = titles.title_id)

语法

EXISTS subquery
参数
subquery:是一个受限的 SELECT 语句 (不允许有 COMPUTE 子句和 INTO 关键字)。有关更多信息，请参见 SELECT 中有关子查询的讨论。

结果类型: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 的查询

这个例 子比较了两个语义类似的查询。第一个查询使用 EXISTS 而第二个查询使用 IN 。注意两个查询 返回相同的信息。

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 的查询

本示例显示查找 与出版商住在同一城市中的作者的两种查询方法：第一种方法使用 = ANY ，第二种方法使用 EXISTS 。注意这两种方法返回相同的信息。

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 的查询

本示例所示查询查找由位于以字母 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 的作用与 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