子查询在SELECT目标列位置
只能是标量子查询
在select中嵌套的子查询的返回值只能是1行，1列
mysql> select * from t1;
+----+------+
| k1 | c1   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from t2;
+----+------+
| k2 | c2   |
+----+------+
|  1 |   10 |
|  2 |    2 |
|  3 |   30 |
+----+------+
3 rows in set (0.00 sec)

mysql> select t1.c1,(select t2.k2,t2.c2 from t2 where k2=1) from t1,t2;
ERROR 1241 (21000): Operand should contain 1 column(s)
mysql>
mysql> select t1.c1,(select t2.c2 from t2) from t1,t2;
ERROR 1242 (21000): Subquery returns more than 1 row
mysql>
mysql> select t1.c1,(select t2.c2 from t2 where k2=1) from t1,t2;
+------+-----------------------------------+
| c1   | (select t2.c2 from t2 where k2=1) |
+------+-----------------------------------+
|    1 |                                10 |
|    2 |                                10 |
|    3 |                                10 |
|    1 |                                10 |
|    2 |                                10 |
|    3 |                                10 |
|    1 |                                10 |
|    2 |                                10 |
|    3 |                                10 |
+------+-----------------------------------+
9 rows in set (0.00 sec)

mysql> select t1.c1,(select t2.c2 from t2 where c2=1) from t1,t2;
+------+-----------------------------------+
| c1   | (select t2.c2 from t2 where c2=1) |
+------+-----------------------------------+
|    1 |                              NULL |
|    2 |                              NULL |
|    3 |                              NULL |
|    1 |                              NULL |
|    2 |                              NULL |
|    3 |                              NULL |
|    1 |                              NULL |
|    2 |                              NULL |
|    3 |                              NULL |
+------+-----------------------------------+
9 rows in set (0.00 sec)


mysql> select t1.c1,(select t2.c2 from t2 where c2>1) from t1,t2;
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> select t1.c1,(select t2.c2 from t2 where c2=10) from t1,t2;
+------+------------------------------------+
| c1   | (select t2.c2 from t2 where c2=10) |
+------+------------------------------------+
|    1 |                                 10 |
|    2 |                                 10 |
|    3 |                                 10 |
|    1 |                                 10 |
|    2 |                                 10 |
|    3 |                                 10 |
|    1 |                                 10 |
|    2 |                                 10 |
|    3 |                                 10 |
+------+------------------------------------+
9 rows in set (0.00 sec)

mysql> insert into t2 values(4,10);
mysql> select t1.c1,(select t2.c2 from t2 where c2=10) from t1,t2;
ERROR 1242 (21000): Subquery returns more than 1 row
mysql>

子查询在FROM字句位置
相关子查询出现在FROM子句中，数据库可能返回类似 "在FROM子句中的子查询无法参考相同查询级别中的关系"的提示，所以相关子查询不能出现在FROM子句中;
非相关子查询出现在FROM子句中，可上拉子查询到父层，在多表连接时统一考虑连接代价然后择优。

相关子查询
mysql> select * from t1,(select * from t2 where t1.k1=t2.k2);
ERROR 1248 (42000): Every derived table must have its own alias
mysql> select * from t1,(select * from t2 where t1.k1=t2.k2) as a_t12;
ERROR 1054 (42S22): Unknown column 't1.k1' in 'where clause'
mysql>


非相关子查询
mysql> select * from t1,(select * from t2);
ERROR 1248 (42000): Every derived table must have its own alias
mysql> select * from t1,(select * from t2) a_t2;
+----+------+----+------+
| k1 | c1   | k2 | c2   |
+----+------+----+------+
|  1 |    1 |  1 |   10 |
|  2 |    2 |  1 |   10 |
|  3 |    3 |  1 |   10 |
|  1 |    1 |  2 |    2 |
|  2 |    2 |  2 |    2 |
|  3 |    3 |  2 |    2 |
|  1 |    1 |  3 |   30 |
|  2 |    2 |  3 |   30 |
|  3 |    3 |  3 |   30 |
|  1 |    1 |  4 |   10 |
|  2 |    2 |  4 |   10 |
|  3 |    3 |  4 |   10 |
+----+------+----+------+
12 rows in set (0.00 sec)

mysql>

子查询出现在WHERE子句位置
出现在WHERE子句中的子查询，是一个条件表达式的一部分，而表达式可以分解为操作符和操作数，根据参与运算的不同数据类型，操作符不尽相同，如INT类型有 >,<,=, <>等操作
这对子查询均有移动的要求(如INT类型的等值操作，要求子查询必须是标量子查询)
另外,子查询出现在WHERE子句中的格式，也有用谓词指定的一些操作，如果IN,BETWEEN,EXISTS等。



mysql> select * from t1 where k1 in (select k2 from t2);
+----+------+
| k1 | c1   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from t1 where k1 >= any(select k2 from t2);
+----+------+
| k1 | c1   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)


mysql> select * from t1 where k1 <= some(select k2 from t2);
+----+------+
| k1 | c1   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from t1 where k1 <= any(select k2 from t2);
+----+------+
| k1 | c1   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from t1 where not exists(select k2 from t2 where k2=100);
+----+------+
| k1 | c1   |
+----+------+
|  1 |    1 |
|  2 |    2 |
|  3 |    3 |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from t1 where exists(select k2 from t2 where k2=100);
Empty set (0.00 sec)

 
JOIN/ON子句位置
JOIN块类似FROM子句
ON块类似WHERE子句
处理方式同FROM子句和WHERE子句


GROUP BY子句位置
目标列必须和GROUP BY关联，可将子查询卸载GROUP BY位置处，但子查询用在GROUP BY处没有实用意义。

ORDER BY子句位置
可将子查询写在ORDER BY位置处，但ORDER BY操作是作用在整条SQL语句上的，子查询用在ORDER BY处没有实用意义。