EXISTS (Transact-SQL)
SQL Server 2005
指定測試資料列是否存在的子查詢。
- subquery
-
這是受限制的 SELECT 陳述式。不允許使用 COMPUTE 子句和 INTO 關鍵字。如需詳細資訊，請參閱＜SELECT (Transact-SQL)＞中子查詢的相關資訊。
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 s WHERE EXISTS (SELECT * FROM Purchasing.Vendor v WHERE s.Name = v.Name) ; GO
下列查詢使用 = ANY。
USE AdventureWorks ; GO SELECT DISTINCT s.Name FROM Sales.Store s WHERE s.Name = ANY (SELECT v.Name FROM Purchasing.Vendor v ) ; GO
D. 利用 EXISTS 和 IN 來比較查詢
下列範例會顯示尋找開頭是 P 之部門員工的查詢。
USE AdventureWorks; GO SELECT c.FirstName, c.LastName, e.Title FROM Person.Contact c JOIN HumanResources.Employee e ON e.ContactID = c.ContactID WHERE EXISTS (SELECT * FROM HumanResources.Department d WHERE e.DepartmentID = d.DepartmentID AND d.Name LIKE 'P%'); GO
下列查詢使用 IN。
USE AdventureWorks; GO SELECT c.FirstName, c.LastName, e.Title FROM Person.Contact c JOIN HumanResources.Employee e ON e.ContactID = c.ContactID WHERE DepartmentID IN (SELECT DepartmentID FROM HumanResources.Department WHERE Name LIKE 'P%'); GO
E. 使用 NOT EXISTS
NOT EXISTS 的作用與 EXISTS 相反。如果子查詢未傳回任何資料列，便滿足 NOT EXISTS 中的 WHERE 子句。下列範例會尋找不在部門中，且名稱開頭是 P 的員工。
USE AdventureWorks; GO SELECT c.FirstName, c.LastName, e.Title FROM Person.Contact c JOIN HumanResources.Employee e ON e.ContactID = c.ContactID WHERE NOT EXISTS (SELECT * FROM HumanResources.Department d WHERE e.DepartmentID = d.DepartmentID 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 (91 row(s) affected)
