适用于：
SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsAnalytics Platform System (PDW)Microsoft Fabric 中的 SQL 终结点Microsoft Fabric 中的仓库Microsoft Fabric SQL 数据库
指定一个子查询，测试行是否存在。
语法
EXISTS ( subquery )
参数
subquery
受限 SELECT 语句。 不允许使用 INTO 关键字。 有关详细信息，请参阅 SELECT 中有关子查询的信息。
返回类型
布尔值
结果值
如果子查询包含任何行，则 TRUE 返回。
示例
本文中的代码示例使用 AdventureWorks2022 或 AdventureWorksDW2022 示例数据库，可以从 Microsoft SQL Server 示例和社区项目 主页下载该数据库。
答： 在 NULL 子查询中使用仍然返回结果集
下面的示例返回在子查询中指定的结果集NULL，并且仍使用EXISTS计算结果TRUE集。
SELECT DepartmentID, Name
FROM HumanResources.Department
WHERE EXISTS (SELECT NULL)
ORDER BY Name ASC;
B. 使用 EXISTS 和 IN 比较查询
以下示例比较了两个语义等同的查询。 第一个查询使用 EXISTS，第二个查询使用 IN。
SELECT a.FirstName,
a.LastName
FROM Person.Person AS a
WHERE EXISTS (SELECT *
FROM HumanResources.Employee AS b
WHERE a.BusinessEntityID = b.BusinessEntityID
AND a.LastName = 'Johnson');
GO
下面的查询使用 IN。
SELECT a.FirstName,
a.LastName
FROM Person.Person AS a
WHERE a.LastName IN (SELECT a.LastName
FROM HumanResources.Employee AS b
WHERE a.BusinessEntityID = b.BusinessEntityID
AND a.LastName = 'Johnson');
GO
下面是任一查询的结果集。
FirstName LastName
-------------------------------------------------- ----------
Barry Johnson
David Johnson
Willis Johnson
C. 使用 EXISTS 和 = ANY 比较查询
以下示例显示两个查找其名称与供应商名称相同的商店的查询。 第一个查询使用 EXISTS，第二个查询使用 = ANY。
SELECT DISTINCT s.Name
FROM Sales.Store AS s
WHERE EXISTS (SELECT *
FROM Purchasing.Vendor AS v
WHERE s.Name = v.Name);
GO
下面的查询使用 = ANY。
SELECT DISTINCT s.Name
FROM Sales.Store AS s
WHERE s.Name = ANY (SELECT v.Name
FROM Purchasing.Vendor AS v);
GO
D. 使用 EXISTS 和 IN 比较查询
以下示例显示查找以 P 开头的部门员工的查询。
SELECT p.FirstName,
p.LastName,
e.JobTitle
FROM Person.Person AS p
INNER JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = p.BusinessEntityID
WHERE EXISTS (SELECT *
FROM HumanResources.Department AS d
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON d.DepartmentID = edh.DepartmentID
WHERE e.BusinessEntityID = edh.BusinessEntityID
AND d.Name LIKE 'P%');
GO
下面的查询使用 IN。
SELECT p.FirstName,
p.LastName,
e.JobTitle
FROM Person.Person AS p
INNER JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = p.BusinessEntityID
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.BusinessEntityID = edh.BusinessEntityID
WHERE edh.DepartmentID IN (SELECT DepartmentID
FROM HumanResources.Department
WHERE Name LIKE 'P%');
GO
E. 使用 NOT EXISTS
NOT EXISTS 工作与 EXISTS.
WHERE如果子查询未返回任何行，则满足该子NOT EXISTS句。 以下示例查找不在具有名称以开头 P的部门的员工。
SELECT p.FirstName,
p.LastName,
e.JobTitle
FROM Person.Person AS p
INNER JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = p.BusinessEntityID
WHERE NOT EXISTS (SELECT *
FROM HumanResources.Department AS d
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON d.DepartmentID = edh.DepartmentID
WHERE e.BusinessEntityID = edh.BusinessEntityID
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 Database Administrator
Amy Alberts European Sales Manager
Sean Alexander Quality Assurance Technician
Pamela Ansman-Wolfe Sales Representative
Zainal Arifin Document Control Manager
David Barber Assistant to CFO
Paula Barreto de Mattos Human Resources Manager
Shai Bassli Facilities Manager
Wanida Benshoof Marketing Assistant
Karen Berg Application Specialist
Karen Berge Document Control Assistant
Andreas Berglund Quality Assurance Technician
Matthias Berndt Shipping & Receiving Clerk
Jo Berry Janitor
Jimmy Bischoff Stocker
Michael Blythe Sales Representative
David Bradley Marketing Manager
Kevin Brown Marketing Assistant
David Campbell Sales Representative
Jason Carlson Information Services Manager
Fernando Caro Sales Representative
Sean Chai Document Control Assistant
Sootha Charncherngkha Quality Assurance Technician
Hao Chen HR Administrative Assistant
Kevin Chrisulis Network Administrator
Pat Coleman Janitor
Stephanie Conroy Network Manager
Debra Core Application Specialist
Ovidiu Crãcium Sr. Tool Designer
Grant Culbertson HR Administrative Assistant
Mary Dempsey Marketing Assistant
Thierry D'Hers Tool Designer
Terri Duffy VP Engineering
Susan Eaton Stocker
Terry Eminhizer Marketing Specialist
Gail Erickson Design Engineer
Janice Galvin Tool Designer
Mary Gibson Marketing Specialist
Jossef Goldberg Design Engineer
Sariya Harnpadoungsataya Marketing Specialist
Mark Harrington Quality Assurance Technician
Magnus Hedlund Facilities Assistant
Shu Ito Sales Representative
Stephen Jiang North American Sales Manager
Willis Johnson Recruiter
Brannon Jones Finance Manager
Tengiz Kharatishvili Control Specialist
Christian Kleinerman Maintenance Supervisor
Vamsi Kuppa Shipping & Receiving Clerk
David Liu Accounts Manager
Vidur Luthra Recruiter
Stuart Macrae Janitor
Diane Margheim Research & Development Enginee
Mindy Martin Benefits Specialist
Gigi Matthew Research & Development Enginee
Tete Mensa-Annan Sales Representative
Ramesh Meyyappan Application Specialist
Dylan Miller Research & Development Manager
Linda Mitchell Sales Representative
Barbara Moreland Accountant
Laura Norman Chief Financial Officer
Chris Norred Control Specialist
Jae Pak Sales Representative
Wanda Parks Janitor
Deborah Poe Accounts Receivable Specialist
Kim Ralls Stocker
Tsvi Reiter Sales Representative
Sharon Salavaria Design Engineer
Ken Sanchez Chief Executive Officer
José Saraiva Sales Representative
Mike Seamans Accountant
Ashvini Sharma Network Administrator
Janet Sheperdigian Accounts Payable Specialist
Candy Spoon Accounts Receivable Specialist
Michael Sullivan Sr. Design Engineer
Dragan Tomic Accounts Payable Specialist
Lynn Tsoflias Sales Representative
Rachel Valdez Sales Representative
Garrett Vargar Sales Representative
Ranjit Varkey Chudukatil Sales Representative
Bryan Walton Accounts Receivable Specialist
Jian Shuo Wang Engineering Manager
Brian Welcker VP Sales
Jill Williams Marketing Specialist
Dan Wilson Database Administrator
John Wood Marketing Specialist
Peng Wu Quality Assurance Supervisor
示例：Azure Synapse Analytics 和 Analytics Platform System (PDW)
F. 使用 EXISTS
以下示例确定 ProspectiveBuyer 表中的行是否与 DimCustomer 表中的行匹配。 仅当两个表中的和BirthDate值匹配时LastName，查询才会返回行。
SELECT a.LastName, a.BirthDate
FROM DimCustomer AS a
WHERE EXISTS (SELECT *
FROM dbo.ProspectiveBuyer AS b
WHERE (a.LastName = b.LastName)
AND (a.BirthDate = b.BirthDate));
G. 使用 NOT EXISTS
NOT EXISTS 的工作方式与相反 EXISTS。
WHERE如果子查询未返回任何行，则满足该子NOT EXISTS句。 以下示例在DimCustomer表中查找表中的行，其中LastNameBirthDate该行与表中的任何条目ProspectiveBuyers都不匹配。
SELECT a.LastName,
a.BirthDate
FROM DimCustomer AS a
WHERE NOT EXISTS (SELECT *
FROM dbo.ProspectiveBuyer AS b
WHERE (a.LastName = b.LastName)
AND (a.BirthDate = b.BirthDate));