DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH PRINT BOOK
 

(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