Subqueries with EXISTS

When a subquery is introduced with the keyword EXISTS, it functions as an existence test. The WHERE clause of the outer query tests for the existence of rows returned by the subquery. The subquery does not actually produce any data; it returns a value of TRUE or FALSE.

A subquery introduced with EXISTS has the following syntax:

WHERE [NOT] EXISTS (subquery)

This query finds the names of all publishers who publish business books:

USE pubs

SELECT pub_name

FROM publishers

WHERE EXISTS

    (SELECT *

    FROM titles

    WHERE pub_id = publishers.pub_id

        AND type = 'business')

Here is the result set:

pub_name

--------------------

New Moon Books

Algodata Infosystems

(2 row(s) affected)

To determine the results of this query, consider each publisher’s name in turn. Does this value cause the subquery to return at least one row? In other words, does it cause the existence test to evaluate to TRUE?

In this case, the first publisher name is Algodata Infosystems, with identification number 1389. Are there any rows in the titles table in which pub_id is 1389 and type is business? If so, Algodata Infosystems should be one of the values selected. The same process is repeated for each of the other publisher names.

Notice that subqueries introduced with EXISTS are a bit different from other subqueries in these ways:

• The keyword EXISTS is not preceded by a column name, constant, or other expression.
• The select list of a subquery introduced by EXISTS almost always consists of an asterisk (*). There is no reason to list column names because you are simply testing for the existence of rows that meet the conditions specified in the subquery.

The EXISTS keyword is important because often there is no alternative, nonsubquery formulation. Although some queries formulated with EXISTS cannot be expressed any other way, all queries that use IN or a comparison operator modified by ANY or ALL can be expressed with EXISTS.

Examples of queries using EXISTS and equivalent alternatives follow.

Here are two ways to find authors who live in the same city as a publisher:

USE pubs

SELECT au_lname, au_fname

FROM authors

WHERE city =ANY

    (SELECT city

    FROM publishers)

-- Or

USE pubs

SELECT au_lname, au_fname

FROM authors

WHERE exists

    (SELECT *

    FROM publishers

    WHERE authors.city = publishers.city)

Here is the result set for either query:

au_lname            au_fname

--------            --------

Carson            Cheryl

Bennet            Abraham

(2 row(s) affected)

These two queries find titles of books published by any publisher located in a city that begins with the letter B:

USE pubs

SELECT title

FROM titles

WHERE pub_id IN

    (SELECT pub_id

    FROM publishers

    WHERE city LIKE 'B%')

-- Or

USE pubs

SELECT title

FROM titles

WHERE EXISTS

    (SELECT *

    FROM publishers

    WHERE pub_id = titles.pub_id

        AND city LIKE 'B%')

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)

See Also

EXISTS