EXISTS (Transact-SQL)
行が存在するかどうかをテストするサブクエリを指定します。
.gif "トピック リンク アイコン")
構文
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 を使用してクエリを比較する
次の例では、意味的に等価な 2 つのクエリを比較します。最初のクエリでは EXISTS を使用し、2 番目のクエリでは 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 を使用してクエリを比較する
次の例では、ベンダと同じ名前の店を検索する 2 つのクエリを示します。最初のクエリでは EXISTS を使用し、2 番目のクエリでは =``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)
参照
関連項目
式 (Transact-SQL)
関数 (Transact-SQL)
WHERE (Transact-SQL)