使用：EXISTS (Transact-SQL)

指定一個子查詢，測試行是否存在。

Transact-SQL 文法約定

文法

EXISTS subquery

參數

subquery

受限制的 SELECT 語句。不允許使用 COMPUTE 子句和 INTO 關鍵字。有關詳細資料，請參閱 SELECT (Transact-SQL) 中有關子查詢的資訊。

結果類型

Boolean

結果值

如果子查詢包含任何行，則返回 TRUE。

樣本
A. 在子查詢中使用 NULL 仍然返回結果集

以下樣本返回在子查詢中指定了 NULL 的結果集，並且通過使用 EXISTS 仍求值為 TRUE。

	複製代碼
USE AdventureWorks ;GOSELECT DepartmentID, Name FROM HumanResources.Department WHERE EXISTS (SELECT NULL)ORDER BY Name ASC ;

B. 比較使用 EXISTS 和 IN 的查詢

以下樣本比較了兩個語義等同的查詢。第一個查詢使用 EXISTS，第二個查詢使用 IN。

	複製代碼
USE AdventureWorks ;GOSELECT DISTINCT c.FirstName, c.LastName, e.DepartmentIDFROM Person.Contact c JOIN HumanResources.Employee eON e.ContactID = c.ContactID WHERE EXISTS(SELECT *FROM HumanResources.Department dWHERE e.DepartmentID = d.DepartmentIDAND d.Name = 'Purchasing') ;GO

下面的查詢使用 IN。

	複製代碼
USE AdventureWorks ;GOSELECT DISTINCT c.FirstName, c.LastName, e.DepartmentIDFROM Person.Contact c JOIN HumanResources.Employee eON e.ContactID = c.ContactID WHERE DepartmentID IN(SELECT DepartmentID FROM HumanResources.Department dWHERE d.Name = 'Purchasing') ;GO

以下是其中任一查詢的結果集。

	複製代碼
FirstName LastName DepartmentID------------------------- ------------------------- ------------Annette Hill 5 Arvind Rao 5 Ben Miller 5 Eric Kurjan 5 Erin Hagins 5 Frank Pellow 5 Fukiko Ogisu 5 Gordon Hee 5 Linda Meisner 5 Mikael Sandberg 5 Reinout Hillmann 5 Sheela Word 5 (12 row(s) affected)

C. 比較使用 EXISTS 和 = ANY 的查詢

以下樣本顯示兩個尋找其名稱與供應商名稱相同的商店的查詢。第一個查詢使用 EXISTS，第二個查詢使用 = ANY。

	複製代碼
USE AdventureWorks ;GOSELECT DISTINCT s.NameFROM Sales.Store s WHERE EXISTS(SELECT *FROM Purchasing.Vendor vWHERE s.Name = v.Name) ;GO

下面的查詢使用 = ANY。

	複製代碼
USE AdventureWorks ;GOSELECT DISTINCT s.NameFROM Sales.Store s WHERE s.Name = ANY(SELECT v.NameFROM Purchasing.Vendor v ) ;GO

D. 比較使用 EXISTS 和 IN 的查詢

以下樣本顯示尋找以 P 開頭的部門員工的查詢。

	複製代碼
USE AdventureWorks;GOSELECT c.FirstName, c.LastName, e.TitleFROM Person.Contact c JOIN HumanResources.Employee eON e.ContactID = c.ContactID WHERE EXISTS(SELECT *FROM HumanResources.Department dWHERE e.DepartmentID = d.DepartmentIDAND d.Name LIKE 'P%');GO

下面的查詢使用 IN。

	複製代碼
USE AdventureWorks;GOSELECT c.FirstName, c.LastName, e.TitleFROM Person.Contact c JOIN HumanResources.Employee eON e.ContactID = c.ContactID WHERE DepartmentID IN(SELECT DepartmentIDFROM HumanResources.DepartmentWHERE Name LIKE 'P%');GO

E. 使用 NOT EXISTS

NOT EXISTS 的作用與 EXISTS 正相反。如果子查詢沒有返回行，則滿足 NOT EXISTS 中的 WHERE 子句。以下樣本尋找不在部門中且姓名以 P 開頭的員工。

	複製代碼
USE AdventureWorks;GOSELECT c.FirstName, c.LastName, e.TitleFROM Person.Contact c JOIN HumanResources.Employee eON e.ContactID = c.ContactID WHERE NOT EXISTS(SELECT *FROM HumanResources.Department dWHERE e.DepartmentID = d.DepartmentIDAND d.Name LIKE 'P%')ORDER BY LastName, FirstNameGO

下面是結果集： 

複製代碼

FirstName LastName Title ------------------------------ ------------------------------ ------------Syed Abbas Pacific Sales Manager Hazem Abolrous Quality Assurance Manager Humberto Acevedo Application Specialist Pilar Ackerman Shipping & Receiving SupervisoFranç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 AssistantThierry 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 EngineeMindy Martin Benefits Specialist Gigi Matthew Research & Development EngineeTete Mensa-Annan Sales Representative Ramesh Meyyappan Application Specialist Dylan Miller Research & Development ManagerLinda 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 SpecialistKim 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 SpecialistMichael 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 SpecialistJian 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 (91 row(s) affected)