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