Can EXISTS BE USED IN subquery?

13.2.11.6 Subqueries with EXISTS or NOT EXISTS

If a subquery returns any rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is 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) 

How does a subquery with exists work?

What is the correct syntax for exists expression in subquery?

Which clauses can use a subquery?

What to use instead of exists?