exists 与 not exists

http://msdn.microsoft.com/zh-cn/library/ms188336.aspx

SQL Server 2008 联机丛书（2009 年 5 月）
EXISTS (Transact-SQL)

指定一个子查询，测试行是否存在。

 Transact-SQL 语法约定

 语法

EXISTS subquery 参数
subquery
受限制的 SELECT 语句。不允许使用 COMPUTE 子句和 INTO 关键字。有关详细信息，请参阅 SELECT (Transact-SQL) 中有关子查询的信息。

 结果类型
Boolean

 结果值
如果子查询包含任何行，则返回 TRUE。

 示例
A. 在子查询中使用 NULL 仍然返回结果集
以下示例返回在子查询中指定了 NULL 时的结果集，并且通过使用 EXISTS 仍然求值为 TRUE。

复制代码
USE AdventureWorks ;
GO
SELECT DepartmentID, Name
FROM HumanResources.Department
WHERE EXISTS (SELECT NULL)
ORDER BY Name ASC ;B. 比较使用 EXISTS 和 IN 的查询
以下示例比较了两个语义等同的查询。第一个查询使用 EXISTS，第二个查询使用 IN。

复制代码
USE AdventureWorks ;
GO
SELECT a.FirstName, a.LastName
FROM Person.Contact AS a
WHERE EXISTS
(SELECT *
    FROM HumanResources.Employee AS b
    WHERE a.ContactId = b.ContactID
    AND a.LastName = 'Johnson');
GO下面的查询使用 IN。

复制代码
USE AdventureWorks ;
GO
SELECT a.FirstName, a.LastName
FROM Person.Contact AS a
WHERE a.LastName IN
(SELECT a.LastName
    FROM HumanResources.Employee AS b
    WHERE a.ContactId = b.ContactID
    AND a.LastName = 'Johnson');
GO以下是其中任一查询的结果集。

复制代码
FirstName                                          LastName
-------------------------------------------------- ----------
Barry                                              Johnson
David                                              Johnson
Willis                                             Johnson
(3 row(s) affected)C. 比较使用 EXISTS 和 = ANY 的查询
以下示例显示两个查找其名称与供应商名称相同的商店的查询。第一个查询使用 EXISTS，第二个查询使用 = ANY。

复制代码
USE AdventureWorks ;
GO
SELECT DISTINCT s.Name
FROM Sales.Store AS s
WHERE EXISTS
(SELECT *
    FROM Purchasing.Vendor AS v
    WHERE s.Name = v.Name) ;
GO下面的查询使用 = ANY。

复制代码
USE AdventureWorks ;
GO
SELECT DISTINCT s.Name
FROM Sales.Store AS s
WHERE s.Name = ANY
(SELECT v.Name
    FROM Purchasing.Vendor AS v ) ;
GOD. 比较使用 EXISTS 和 IN 的查询
以下示例显示查找以 P 开头的部门员工的查询。

复制代码
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, e.Title
FROM Person.Contact AS c
JOIN HumanResources.Employee AS e
   ON e.ContactID = c.ContactID
WHERE EXISTS
(SELECT *
    FROM HumanResources.Department AS d
    JOIN HumanResources.EmployeeDepartmentHistory AS edh
       ON d.DepartmentID = edh.DepartmentID
    WHERE e.EmployeeID = edh.EmployeeID
    AND d.Name LIKE 'P%');
GO下面的查询使用 IN。

复制代码
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, e.Title
FROM Person.Contact AS c JOIN HumanResources.Employee AS e
   ON e.ContactID = c.ContactID
JOIN HumanResources.EmployeeDepartmentHistory AS edh
   ON e.EmployeeID = edh.EmployeeID
WHERE edh.DepartmentID IN
(SELECT DepartmentID
   FROM HumanResources.Department
   WHERE Name LIKE 'P%');
GOE. 使用 NOT EXISTS
NOT EXISTS 的作用与 EXISTS 正相反。如果子查询没有返回行，则满足 NOT EXISTS 中的 WHERE 子句。以下示例查找不在部门中且姓名以 P 开头的员工。

复制代码
SELECT c.FirstName, c.LastName, e.Title
FROM Person.Contact AS c
JOIN HumanResources.Employee AS e
   ON e.ContactID = c.ContactID
WHERE NOT EXISTS
(SELECT *
   FROM HumanResources.Department AS d
   JOIN HumanResources.EmployeeDepartmentHistory AS edh
      ON d.DepartmentID = edh.DepartmentID
   WHERE e.EmployeeID = edh.EmployeeID
   AND d.Name LIKE 'P%')
ORDER BY LastName, FirstName
GO下面是结果集：

复制代码
FirstName                      LastName                       Title                        
------------------------------ ------------------------------ ------------
Syed                           Abbas                          Pacific Sales Manager        
Hazem                          Abolrous                       Quality Assurance Manager    
Humberto                       Acevedo                        Application Specialist       
Pilar                          Ackerman                       Shipping & Receiving Superviso
Fran?ois                       Ajenstat       &nbsa