Movatterモバイル変換

MySQL 9.2 Reference Manual

15.2.15.6 Subqueries with EXISTS or NOT EXISTS

If a subquery returns any rows at all,EXISTSsubquery isTRUE, andNOT EXISTSsubquery isFALSE. For example:

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

Traditionally, anEXISTS subquery starts withSELECT *, but it could begin withSELECT 5 orSELECT column1 or anything at all. MySQL ignores theSELECT list in such a subquery, so it makes no difference.

For the preceding example, ift2 contains any rows, even rows with nothing butNULL values, theEXISTS 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-nestedNOT EXISTS query. That is, it has aNOT EXISTS clause within aNOT 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 nestedNOT EXISTS answers the question“isxTRUE for ally?”

You can also useNOT EXISTS orNOT EXISTS withTABLE in the subquery, like this:

SELECT column1 FROM t1 WHERE EXISTS (TABLE t2);

The results are the same as when usingSELECT * with noWHERE clause in the subquery.

PREV HOME UP NEXT