另外一个IN非相关子查询，查询执行计划如下：
| mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 IN (SELECT a2 FROM t2 WHERE t1.a1=10); |
| +----+-------------+-------+------+------------------------------------------------------------------+ | id | select_type | table | type | Extra | +----+-------------+-------+------+------------------------------------------------------------------+ | 1 | SIMPLE | t2 | ALL | Using where; Start temporary | | 1 | SIMPLE | t1 | ALL | Using where; End temporary; Using join buffer (Block Nested Loop)| +----+-------------+-------+------+------------------------------------------------------------------+ 2 rows in set, 2 warnings (0.00 sec) |
| /* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`, `test`.`t1`.`b1` AS `b1` from `test`.`t1` semi join (`test`.`t2`) where ((`test`.`t2`.`a2` = 10) and (`test`.`t1`.`a1` = 10)) |
从查询执行计划看，子子查询不存在，表t1和t2直接做了块嵌套循环连接（Block Nested Loop），但属于半连接操作（semi join），把子查询上拉到父查询中用嵌套循环半连接完成IN操作。
示例四，MySQL支持对NOT IN类型的子查询的优化
NOT IN非相关子查询，查询执行计划如下：
| mysql> EXPLAIN EXTENDED SELECT * FROM t1 WHERE t1.a1 NOT IN (SELECT a2 FROM t2 WHERE t2.a2>10); |
| +----+-------------+-------+------+------+-------------+ | id | select_type | table | type | key | Extra | +----+-------------+-------+------+------+-------------+ | 1 | PRIMARY | t1 | ALL | NULL | Using where | | 2 | SUBQUERY | t2 | ALL | NULL | Using where | +----+-------------+-------+------+------+-------------+ 2 rows in set, 1 warning (0.02 sec) |
被查询优化器处理后的语句为：
| /* select#1 */ select `test`.`t1`.`id1` AS `id1`,`test`.`t1`.`a1` AS `a1`, `test`.`t1`.`b1` AS `b1` from `test`.`t1` where (not(<in_optimizer>( `test`.`t1`.`a1`,`test`.`t1`.`a1` in ( <materialize> (/* select#2 */ select `test`.`t2`.`a2` from `test`.`t2` where (`test`.`t2`.`a2` > 10) having 1 ), <primary_index_lookup>( `test`.`t1`.`a1` in <temporary table> on <auto_key> where ((`test`.`t1`.`a1` = `materialized-subquery`.`a2`)) ) ) )) ) |
从查询执行计划看，表t2做了子查询（SUBQUERY）。而子查询被物化（materialize）。所以，MySQL对于NOT IN子查询采用了物化的优化方式，但不支持子查询的消除。
另外一个NOT IN非相关子查询，查询执行计划如下：
