Use the exists keyword with a subquery to test for the existence of some result from the subquery:
{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 (see “Using correlated subqueries”).
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
