13.2.11.6 Subqueries with EXISTS or NOT EXISTS
If a subquery returns any rows at all, EXISTS
is subquery
TRUE
, and NOT EXISTS
is subquery
FALSE
. For example:
SELECT column1 FROM t1 WHERE EXISTS [SELECT * FROM t2];
Traditionally, an EXISTS
subquery starts with SELECT *
, but it could begin with SELECT 5
or SELECT column1
or anything at all. MySQL ignores the
SELECT
list in such a subquery, so it makes no difference.
For the preceding example, if t2
contains any rows, even rows with nothing but NULL
values, the EXISTS
condition is TRUE
. This is actually an unlikely example because a [NOT] EXISTS
subquery almost always contains correlations. Here are some more realistic examples:
What kind of store is present in one or more cities?
SELECT DISTINCT store_type FROM stores WHERE EXISTS [SELECT * FROM cities_stores WHERE cities_stores.store_type = stores.store_type];
What kind of store is present in no cities?
SELECT DISTINCT store_type FROM stores WHERE NOT EXISTS [SELECT * FROM cities_stores WHERE cities_stores.store_type = stores.store_type];
What kind of store is present in all cities?
SELECT DISTINCT store_type FROM stores s1 WHERE NOT EXISTS [ SELECT * FROM cities WHERE NOT EXISTS [ SELECT * FROM cities_stores WHERE cities_stores.city = cities.city AND cities_stores.store_type = stores.store_type]];
The last example is a double-nested NOT
EXISTS
query. That is, it has a NOT
EXISTS
clause within a NOT EXISTS
clause. Formally, it answers the question “does a city exist with a store that is not in Stores
”? But it is easier to say that a nested NOT EXISTS
answers the
question “is x
TRUE
for all y
?”
In MySQL 8.0.19 and later, you can also use NOT
EXISTS
or NOT EXISTS
with TABLE
in the subquery, like this:
SELECT column1 FROM t1 WHERE EXISTS [TABLE t2];
The results are the same as when using SELECT
*
with no WHERE
clause in the subquery.
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 [2 rows affected]
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 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’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 or nonexistence 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 [2 rows affected]
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 [11 rows affected]