15.2.15.6 Subqueries with EXISTS or NOT EXISTS (original) (raw)

15.2.15.6 Subqueries with EXISTS or NOT EXISTS

If a subquery returns any rows at all, EXISTS_`subquery`_ isTRUE, and NOT EXISTS_`subquery`_ isFALSE. For example:

SELECT column1 FROM t1 WHERE EXISTS (SELECT * FROM t2);

Traditionally, an EXISTS subquery starts withSELECT *, but it could begin withSELECT 5 or SELECT column1 or anything at all. MySQL ignores theSELECT 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 isTRUE. 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  
  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 inStores”? 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 withTABLE 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.