您现在的位置是:首页 > SQLServer

SQLServer

Exists用法

2020-11-21 11:29:45 SQLServer admin
exists 与 not existshttp://msdn.microsoft.com/zh-cn/library/ms188336.aspxSQL Server 2008 联机丛书(2009 年 5 月)EXISTS (Transact

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