子查询之ALL/ANY/SOME重写思路

SOME与ANY的含义相同，且 =ANY 或 =SOME 都等价于IN
select emp_id from t1 where owner =ANY (select name from t2 where id=100);
select emp_id from t1 where owner =SOME (select name from t2 where id=100);
上面都等价于：
select emp_id from t1 where owner in (select name from t2 where id=100);

NOT IN 与 <>ALL的含义相同，但与<>ANY含义不同

对于ALL/ANY/SOME类子查询，条件是非等值比较，如果子查询中没有GROUP BY等聚集函数，可以用MIN/MAX做等价转换
根据ALL/ANY/SOME的不同，以及后面的大于，小于号的不同，又分为好多种情形，下面只列出一种
select emp_id from t1 where age >ALL (select age from t2);
等价于：
select emp_id from t1 where age >MAX (select age from t2);

以上参考：《数据库查询优化器的艺术》，P26

对于=ALL的子查询，且是非相关子查询，用EXISTS strategy方式优化
select * from t1 where t1.age =ALL (select age from t2 where t2.age=10)
t1.age为非空时。优化器利用exists strategy重写为：select * from t1 where (t1.age EXISTS (SELECT 1 FROM t2 where t2.age=10 AND t1.age=t2.age)
(P383-P384)
思考，这是优化器的行为，但是优化点在哪里？
这个问题，在官网时这么解释的：
Consider the following subquery comparison:

outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where)

MySQL evaluates queries “from outside to inside.” That is, it first obtains the value of the outer expression outer_expr, and then runs the subquery and captures the rows that it produces.

A very useful optimization is to “inform” the subquery that the only rows of interest are those where the inner expression inner_expr is equal to outer_expr. This is done by pushing down an appropriate equality into the subquery's WHERE clause to make it more restrictive. The converted comparison looks like this:

EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr=inner_expr)

After the conversion, MySQL can use the pushed-down equality to limit the number of rows it must examine to evaluate the subquery.

参考：
1.MySQL子查询---EXISTS优化策略辨析
2.Optimizing Subqueries with the EXISTS Strategy