13.2.10.7 Correlated Subqueries注释

13.2.10.7 Correlated Subqueries

A correlated subquery is a subquery that contains a reference to a table that also appears in the outer query. For example:

SELECT * FROM t1
  WHERE column1 = ANY (SELECT column1 FROM t2
                       WHERE t2.column2 = t1.column2);

Notice that the subquery contains a reference to a column of t1, even though the subquery's FROM clause does not mention a table t1. So, MySQL looks outside the subquery, and finds t1 in the outer query.

Suppose that table t1 contains a row where column1 = 5 and column2 = 6; meanwhile, table t2 contains a row where column1 = 5 and column2 = 7. The simple expression ... WHERE column1 = ANY (SELECT column1 FROM t2) would be TRUE, but in this example, the WHERE clause within the subquery is FALSE (because(5,6) is not equal to (5,7)), so the expression as a whole is FALSE.

Scoping rule: MySQL evaluates from inside to outside. For example:

SELECT column1 FROM t1 AS x
  WHERE x.column1 = (SELECT column1 FROM t2 AS x
    WHERE x.column1 = (SELECT column1 FROM t3
      WHERE x.column2 = t3.column1));

In this statement, x.column2 must be a column in table t2 because SELECT column1 FROM t2 AS x ...renames t2. It is not a column in table t1 because SELECT column1 FROM t1 ... is an outer query that isfarther out.

For subqueries in HAVING or ORDER BY clauses, MySQL also looks for column names in the outer select list.

For certain cases, a correlated subquery is optimized. For example:

val IN (SELECT key_val FROM tbl_name WHERE correlated_condition)

Otherwise, they are inefficient and likely to be slow. Rewriting the query as a join might improve performance.

Aggregate functions in correlated subqueries may contain outer references, provided the function contains nothing but outer references, and provided the function is not contained in another function or expression.

5.6.17试的结果，子查询被优化成join了：
mysql> show create table one\G
*************************** 1. row ***************************
       Table: one
Create Table: CREATE TABLE `one` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)


mysql> show create table two\G
*************************** 1. row ***************************
       Table: two
Create Table: CREATE TABLE `two` (
  `id2` int(11) NOT NULL DEFAULT '0',
  `name2` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id2`),
  KEY `i_name2` (`name2`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> explain select * from one where id=any(select id2 from two where one.name=two.name2);
+----+-------------+-------+-------+-----------------+---------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type  | possible_keys   | key     | key_len | ref  | rows | Extra                                              |
+----+-------------+-------+-------+-----------------+---------+---------+------+------+----------------------------------------------------+
|  1 | SIMPLE      | two   | index | PRIMARY,i_name2 | i_name2 | 13      | NULL |    3 | Using index                                        |
|  1 | SIMPLE      | one   | ALL   | PRIMARY         | NULL    | NULL    | NULL |    3 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+-------+-----------------+---------+---------+------+------+----------------------------------------------------+
2 rows in set (0.00 sec)

5.5.37里仍然是子查询：
mysql> explain select * from one where id=any(select id2 from two where one.name=two.name2);
+----+--------------------+-------+-----------------+-----------------+---------+---------+------+------+-------------+
| id | select_type        | table | type            | possible_keys   | key     | key_len | ref  | rows | Extra       |
+----+--------------------+-------+-----------------+-----------------+---------+---------+------+------+-------------+
|  1 | PRIMARY            | one   | ALL             | NULL            | NULL    | NULL    | NULL |    3 | Using where |
|  2 | DEPENDENT SUBQUERY | two   | unique_subquery | PRIMARY,i_name2 | PRIMARY | 4       | func |    1 | Using where |
+----+--------------------+-------+-----------------+-----------------+---------+---------+------+------+-------------+
2 rows in set (2.31 sec)