아티클
04/01/2012

EXISTS(Transact-SQL)

하위 쿼리를 지정하여 행의 존재 여부를 테스트합니다.

구문

EXISTS subquery

인수

subquery
제한된 SELECT 문입니다. COMPUTE 절과 INTO 키워드는 사용할 수 없습니다. 자세한 내용은 SELECT(Transact-SQL)에서 하위 쿼리에 대한 내용을 참조하십시오.

결과 유형

Boolean

결과 값

하위 쿼리에 행이 있으면 TRUE를 반환합니다.

예

1. 하위 쿼리에서 NULL을 사용하여 결과 집합 계속 반환

다음 예에서는 하위 쿼리에 NULL을 지정하여 결과 집합을 반환하고 EXISTS를 사용하여 TRUE로 계속 평가됩니다.

USE AdventureWorks2008R2 ;
GO
SELECT DepartmentID, Name 
FROM HumanResources.Department 
WHERE EXISTS (SELECT NULL)
ORDER BY Name ASC ;

2. EXISTS 및 IN을 사용하여 쿼리 비교

다음 예에서는 기능이 동일한 두 쿼리를 비교합니다. 첫 번째 쿼리에서는 EXISTS를 사용하고 두 번째 쿼리에서는 IN을 사용합니다.

USE AdventureWorks2008R2 ;
GO
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을 사용합니다.

USE AdventureWorks2008R2 ;
GO
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

(3 row(s) affected)

3. EXISTS 및 = ANY를 사용하여 쿼리 비교

다음 예에서는 공급업체와 이름이 동일한 상점을 찾는 두 개의 쿼리를 보여 줍니다. 첫 번째 쿼리에서는 EXISTS를 사용하고 두 번째 쿼리에서는 =ANY를 사용합니다.

USE AdventureWorks2008R2 ;
GO
SELECT DISTINCT s.Name
FROM Sales.Store AS s 
WHERE EXISTS
(SELECT *
    FROM Purchasing.Vendor AS v
    WHERE s.Name = v.Name) ;
GO

다음 쿼리에서는 = ANY를 사용합니다.

USE AdventureWorks2008R2 ;
GO
SELECT DISTINCT s.Name
FROM Sales.Store AS s 
WHERE s.Name = ANY
(SELECT v.Name
    FROM Purchasing.Vendor AS v ) ;
GO

4. EXISTS 및 IN을 사용하여 쿼리 비교

다음 예에서는 P로 시작되는 부서의 직원을 찾는 쿼리를 보여 줍니다.

USE AdventureWorks2008R2;
GO
SELECT p.FirstName, p.LastName, e.JobTitle
FROM Person.Person AS p 
JOIN HumanResources.Employee AS e
   ON e.BusinessEntityID = p.BusinessEntityID 
WHERE EXISTS
(SELECT *
    FROM HumanResources.Department AS d
    JOIN HumanResources.EmployeeDepartmentHistory AS edh
       ON d.DepartmentID = edh.DepartmentID
    WHERE e.BusinessEntityID = edh.BusinessEntityID
    AND d.Name LIKE 'P%');
GO

다음 쿼리에서는 IN을 사용합니다.

USE AdventureWorks2008R2;
GO
SELECT p.FirstName, p.LastName, e.JobTitle
FROM Person.Person AS p JOIN HumanResources.Employee AS e
   ON e.BusinessEntityID = p.BusinessEntityID 
JOIN HumanResources.EmployeeDepartmentHistory AS edh
   ON e.BusinessEntityID = edh.BusinessEntityID 
WHERE edh.DepartmentID IN
(SELECT DepartmentID
   FROM HumanResources.Department
   WHERE Name LIKE 'P%');
GO

5. NOT EXISTS 사용

NOT EXISTS는 EXISTS와 반대됩니다. 하위 쿼리에서 반환되는 행이 없는 경우에는 NOT EXISTS의 WHERE 절 조건이 충족됩니다. 다음 예에서는 이름이 P로 시작되는 부서에 속하지 않는 직원을 찾습니다.

SELECT p.FirstName, p.LastName, e.JobTitle
FROM Person.Person AS p 
JOIN HumanResources.Employee AS e
   ON e.BusinessEntityID = p.BusinessEntityID 
WHERE NOT EXISTS
(SELECT *
   FROM HumanResources.Department AS d
   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

(91 row(s) affected)

참고 항목

참조

식(Transact-SQL)

기본 제공 함수(Transact-SQL)

WHERE(Transact-SQL)

다음을 통해 공유