Subqueries Introduced 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)

To find the names of all publishers who publish business books, type:

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

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 since you are simply testing for the existence of rows that meet the conditions specified in the subquery.

The EXISTS keyword is very important because there is often no alternative, non-subquery 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.