Subqueries with EXISTS

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