適用於：
sql Server Azure SQL 資料庫 Azure SQL 受控執行個體Azure Synapse Analytics Platform System （PDW） SQL 分析端點在 Microsoft Fabric SQL 資料庫中的 Microsoft 網狀架構倉儲中Microsoft網狀架構
指定測試資料列是否存在的子查詢。
語法
EXISTS ( subquery )
引數
子查詢
受限制 SELECT 的語句。 不允許關鍵詞 INTO 。 如需詳細資訊，請參閱 SELECT 中子查詢的相關信息。
傳回類型
布林值
結果值
如果子查詢包含任何資料列，則傳 TRUE 回 。
範例
本文中的程式代碼範例會使用 AdventureWorks2022 或 AdventureWorksDW2022 範例資料庫，您可以從 Microsoft SQL Server 範例和社群專案 首頁下載。
A。 在 NULL 子查詢中使用 ，仍然傳回結果集
下列範例會傳回在子查詢中指定的結果集NULL，而且仍然使用 TRUE評估為 EXISTS 。
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 資料表中的資料列相符。 只有在兩個數據表中的 和 LastName 值相符時BirthDate，查詢才會傳回數據列。
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 列，其中 LastName 和 BirthDate 不符合數據表中的任何 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));