Subqueries Testing Existence

When a subquery is introduced with the keyword EXISTS, the subquery functions as an “existence test.” The EXISTS keyword in a WHERE clause tests for the existence or nonexistence of data that meets the criteria of the subquery.

A subquery introduced with EXISTS takes this general form:

					Start of SELECT, INSERT, UPDATE, DELETE statement; or
					subquery
WHERE [NOT] EXISTS
     (subquery)
[End of SELECT, INSERT, UPDATE, DELETE statement; or
					subquery]
				

To find the names of all the publishers that publish business books, the query is this:

select distinct pub_name
from publishers
where exists
   (select *
    from titles
    where pub_id = publishers.pub_id
    and type = 'business')
pub_name
========================================
New Age Books
Algodata Infosystems
[2 rows]

EXISTS tests for the presence or absence of the “empty set” of rows.

• If the subquery returns at least one row, the subquery evaluates to “true.” This means that an EXISTS phrase will succeed and a NOT EXISTS phrase will fail.

• If the subquery returns the empty set (no rows), the subquery evaluates to “false.” This means that a NOT EXISTS phrase will succeed and an EXISTS phrase will fail.

In the preceding query, the first publisher's name is Algodata Infosystems, with an identification number of 1389. Does Algodata Infosystems pass the existence test? That is, 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 publishers' names.

Notice that the syntax of subqueries introduced with EXISTS differs a bit from the syntax of other subqueries, in these ways:

• The keyword EXISTS is not preceded by a column name, a constant, or another expression.

• The select list of a subquery introduced by EXISTS almost always consists of an asterisk (*). There is no real point in listing column names because you are simply testing for the existence of rows that meet the subquery's conditions and these are spelled out in the subquery WHERE clause, not in the subquery SELECT clause.

The EXISTS keyword is very important because there is often no alternative, nonsubquery formulation. In practice, an EXISTS subquery is almost always a correlated subquery. Instead of having the outer query operate on values that the inner query supplies, the outer query presents values, one by one, that the inner query tests.

You can use EXISTS to express all “list” subqueries that would use IN, ANY, or ALL. Some examples of statements using EXISTS and their equivalent alternatives follow.

Figure 8.5 shows two queries that find titles of books published by any publisher located in a city that begins with the letter B. Both queries produce the same results. The results show that twelve of the books in the titles table are published by a publisher located in either Boston or Berkeley.

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