sql中 in 、not in 、exists、not exists 用法和差别http://blog.csdn.net/lihan6415151528/article/details/4466914

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