EXISTS

Specifies a subquery to test for the existence of rows.

Syntax

EXISTS subquery

Arguments

subquery

Is a restricted SELECT statement (the COMPUTE clause, and the INTO keyword are not allowed). For more information, see the discussion of subqueries in SELECT.

Result Types

Boolean

Result Values

Returns TRUE if a subquery contains any rows.

Examples

A. Use NULL in subquery to still return a result set

This example returns a result set with NULL specified in the subquery and still evaluates to TRUE by using EXISTS.

USE Northwind
GO
SELECT CategoryName
FROM Categories
WHERE EXISTS (SELECT NULL)
ORDER BY CategoryName ASC
GO

B. Compare queries using EXISTS and IN

This example compares two queries that are semantically equivalent. The first query uses EXISTS and the second query uses IN. Note that both queries return the same information.

USE pubs
GO
SELECT DISTINCT pub_name
FROM publishers
WHERE EXISTS
   (SELECT *
   FROM titles
   WHERE pub_id = publishers.pub_id
   AND type = 'business')
GO

-- Or, using the IN clause:

USE pubs
GO
SELECT distinct pub_name
FROM publishers
WHERE pub_id IN
   (SELECT pub_id
   FROM titles
   WHERE type = 'business')
GO

Here is the result set for either query:

pub_name                                 
---------------------------------------- 
Algodata Infosystems                     
New Moon Books                           

(2 row(s) affected)

C. Compare queries using EXISTS and = ANY

This example shows two queries to find authors who live in the same city as a publisher. The first query uses = ANY and the second uses EXISTS. Note that both queries return the same information.

USE pubs
GO
SELECT au_lname, au_fname
FROM authors
WHERE exists
   (SELECT *
   FROM publishers
   WHERE authors.city = publishers.city)
GO

-- Or, using = ANY

USE pubs
GO
SELECT au_lname, au_fname
FROM authors
WHERE city = ANY
   (SELECT city
   FROM publishers)
GO

Here is the result set for either query:

au_lname                                 au_fname             
---------------------------------------- -------------------- 
Carson                                   Cheryl               
Bennet                                   Abraham              

(2 row(s) affected)

D. Compare queries using EXISTS and IN

This example shows queries to find titles of books published by any publisher located in a city that begins with the letter B.

USE pubs
GO
SELECT title
FROM titles
WHERE EXISTS
   (SELECT *
   FROM publishers
   WHERE pub_id = titles.pub_id
   AND city LIKE 'B%')
GO

-- Or, using IN:

USE pubs
GO
SELECT title
FROM titles
WHERE pub_id IN
   (SELECT pub_id
   FROM publishers
   WHERE city LIKE 'B%')
GO

Here is the result set for either query:

title                                                                            
------------------------------------------------------------------------ 
The Busy Executive's Database Guide                                              
Cooking with Computers: Surreptitious Balance Sheets                             
You Can Combat Computer Stress!                                                  
Straight Talk About Computers                                                    
But Is It User Friendly?                                                         
Secrets of Silicon Valley                                                        
Net Etiquette                                                                    
Is Anger the Enemy?                                                              
Life Without Fear                                                                
Prolonged Data Deprivation: Four Case Studies                                    
Emotional Security: A New Algorithm                                              

(11 row(s) affected)

E. Use NOT EXISTS

NOT EXISTS works the opposite as EXISTS. The WHERE clause in NOT EXISTS is satisfied if no rows are returned by the subquery. This example finds the names of publishers who do not publish business books.

USE pubs
GO
SELECT pub_name
FROM publishers
WHERE NOT EXISTS
   (SELECT *
   FROM titles
   WHERE pub_id = publishers.pub_id
   AND type = 'business')
ORDER BY pub_name
GO

Here is the result set:

pub_name                                 
---------------------------------------- 
Binnet & Hardley                         
Five Lakes Publishing                    
GGG&G                                    
Lucerne Publishing                       
Ramona Publishers                        
Scootney Books                           

(6 row(s) affected)

See Also

Expressions

Functions

WHERE