Subqueries Used with exists

Use the exists keyword with a subquery to test for the existence of some result from the subquery.

The syntax is:

{where | having} [not] exists (subquery)

That is, the where clause of the outer query tests for the existence of the rows returned by the subquery. The subquery does not actually produce any data, but returns a value of TRUE or FALSE.

For example, this query finds the names of all the publishers who publish business books:

select 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

To conceptualize the resolution 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 the results of the preceding query, the second publisher’s name is Algodata Infosystems, which has an identification number of 1389. Are there any rows in the titles table for which the 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’s names.

A subquery that is introduced with exists is different from other subqueries, in these ways:

The keyword exists is not preceded by a column name, constant, or other expression.
The subquery exists evaluates to TRUE or FALSE rather than returning any data.
The select list of the subquery usually consists of the asterisk (*). You need not specify column names, since you are simply testing for the existence of rows that meet the conditions specified in the subquery. Otherwise, the select list rules for a subquery introduced with exists are identical to those for a standard select list.

The exists keyword is very important, because there is often no alternative non-subquery formulation. In practice, a subquery introduced by exists is always a correlated subquery.

Although you cannot express some queries formulated with exists in any other way, you can express all queries that use in or a comparison operator modified by any or all with exists. Some examples of statements using exists and their equivalent alternatives follow.

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

select au_lname, au_fname 
from authors 
where city = any 
   (select city 
    from publishers)

select au_lname, au_fname 
from authors 
where exists 
   (select * 
    from publishers 
    where authors.city = publishers.city)

au_lname          au_fname 
--------------    -------------- 
Carson            Cheryl 
Bennet            Abraham

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

select title 
from titles 
where exists 
   (select * 
    from publishers 
    where pub_id = titles.pub_id 
    and city like "B%")

select title 
from titles 
where pub_id in 
   (select pub_id 
    from publishers 
    where city like "B%")

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