다음을 통해 공유


EXISTS(Transact-SQL)

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

항목 링크 아이콘Transact-SQL 구문 표기 규칙

구문

EXISTS subquery

인수

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

결과 유형

Boolean

결과 값

하위 쿼리에 행이 포함된 경우 TRUE를 반환합니다.

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

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

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

2. 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)

3. 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

4. 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

5. 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?is                       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?ium                        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)

도움말 및 정보

SQL Server 2005 지원 받기