EXISTS (T-SQL)

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 same as EXISTS, except the WHERE clause in which NOT EXISTS is used 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