MySql中的子查询关键字用法

ALL、ANY、SOME

• 形式：

operand comparison_operator keyword (subquery)

其中，comparison_operator是这些操作符中的一个：

= > < >= <= <> !=

keyword是All、Any、Some中的一个，下边分别说明：

• ALL：
  ALL关键字当操作数和子查询返回的所有值比较都为true的时候返回true：

SELECT s1 FROM t1 WHERE s1 > ALL (SELECT s1 FROM t2);

如果t1表中某行记录的s1大于所有t2表中所有的s1，那么该行记录的 >ALL 返回true。如果t2是空表，那么>ALL比较也会返回true。
需要注意的是，如果t2中的s1查询存在null值，那么所有的比较都会是unknown。

• ANY
  ANY关键字当操作数和子查询返回的某一个值比较为true的时候返回true：

SELECT s1 FROM t1 WHERE s1 > ANY (SELECT s1 FROM t2);

如果t1表中某行记录的s1大于t2表中任意一个s1，那么 > ANY 返回true。如果t2是空表，那么 > ANY 返回的结果为false。
需要注意的是，如果t2表查出来的结果全是null，那么>ANY返回的结果是unknown；如果t2表中正常值和null值同时存在，那么>ANY将会正常比较除了null的值并正确返回。

• SOME
  作用和ANY相同，为了解决某些情况下的语义问题而设，具体例子看：
  https://dev.mysql.com/doc/refman/5.7/en/any-in-some-subqueries.html

IN / NOT IN

IN关键字等价于 =ANY，NOT IN关键字等价于 !=ALL，例如，下面两组语句都是相等的：

1.SELECT s1 FROM t1 WHERE s1 = ANY (SELECT s1 FROM t2); 
2.SELECT s1 FROM t1 WHERE s1 IN (SELECT s1 FROM t2);

1.SELECT s1 FROM t1 WHERE s1 <> ALL (SELECT s1 FROM t2); 
2.SELECT s1 FROM t1 WHERE s1 NOT IN (SELECT s1 FROM t2);

EXISTS、NOT EXIST

如果一个子查询(subquery)返回任何一条记录，那么exist (subquery)返回true而not exist (subquery)返回false：

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

通常来说，exist子查询都写作select *，但是它也可以写为select 5或者select col1，MySQL会在exist子查询忽略这些区别，所以上边的写法都是一样的。
在上边的例子中，如果t2包含任意行，甚至只包含空值的行，那么exist都会返回true.
exist/not exist通常用于相关子查询里边，例如：

--查询什么类型的store出现在一个或多个城市中
SELECT DISTINCT store_type 
FROM stores 
WHERE EXISTS (
    SELECT * FROM cities_stores 
    WHERE cities_stores.store_type = stores.store_type
);