(mysql.info.gz) EXISTS and NOT EXISTS subqueries
Info Catalog
(mysql.info.gz) Row subqueries
(mysql.info.gz) Subqueries
(mysql.info.gz) Correlated subqueries
13.1.8.6 `EXISTS' and `NOT EXISTS'
..................................
If a subquery returns any rows at all, then `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 doesn't
matter.
For the preceding example, if `t2' contains any rows, even rows with
nothing but `NULL' values, then the `EXISTS' condition is `TRUE'. This
is actually an unlikely example, since almost always a `[NOT] EXISTS'
subquery will contain 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's easier to say that a nested `NOT EXISTS' answers
the question "is x TRUE for all y?"
Info Catalog
(mysql.info.gz) Row subqueries
(mysql.info.gz) Subqueries
(mysql.info.gz) Correlated subqueries
automatically generated byinfo2html