Chapter 13 SQL Statements

13.2.11.6 Subqueries with EXISTS or NOT EXISTS具有EXISTS或NOT EXISTS的子查询

~~If a subquery returns any rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE.~~ 如果子查询返回任何行，则EXISTS subquery为TRUE，NOT EXISTSsubquery为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.~~ 传统上，EXISTS子查询以SELECT *开头，但它可以以SELECT 5或SELECT column1开头，或者任何形式。~~MySQL ignores the SELECT list in such a subquery, so it makes no difference.~~MySQL在这样的子查询中忽略SELECT列表，所以这没有什么区别。

~~For the preceding example, if t2 contains any rows, even rows with nothing but NULL values, the EXISTS condition is TRUE.~~ 对于前面的示例，如果t2包含任何行，甚至是只有空值的行，那么EXISTS条件为TRUE。~~This is actually an unlikely example because a [NOT] EXISTS subquery almost always contains correlations.~~ 这实际上是一个不太可能的例子，因为[NOT] EXISTS子查询几乎总是包含相关性。~~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.~~ 最后一个例子是一个双嵌套的NOT EXISTS查询。~~That is, it has a NOT EXISTS clause within a NOT EXISTS clause.~~ 也就是说，它在NOT EXISTS子句中有NOT EXISTS子句。~~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?”~~但是嵌套的NOT EXISTS更容易回答“x对所有y都是真的吗？”

~~In MySQL 8.0.19 and later, you can also use NOT EXISTS or NOT EXISTS with TABLE in the subquery, like this:~~在MySQL 8.0.19及更高版本中，子查询中还可以使用NOT EXISTS或NOT EXISTS配合TABLE，如下所示：

SELECT column1 FROM t1 WHERE EXISTS (TABLE t2);

~~The results are the same as when using SELECT * with no WHERE clause in the subquery.~~结果与在子查询中使用SELECT *而不使用WHERE子句时相同。