SQL-子查询
SQL子查询是嵌套在另一个SQL查询中的SELECT语句，将内部查询的结果作为外部查询的条件或者数据源。
核心概念
子查询是一个完整的SELECT语句，可以嵌入到其他查询的where, from, SELECT, HAVING等子句中，用于动态生成条件或临时数据集。
例如
列出刘成所在系的全部同学的姓名
SELECTname
FROMstudent
WHEREdept = (SELECT dept FROM student WHERE name = '刘成')
分类
按返回结果集区分子查询
标量子查询
只返回单一值的子查询称之为标量子查询
例如
select * from t1 where m1 = (select min(m2) from t2)
select (select m1 from t1 limit 1)
行子查询
返回一条记录的子查询，这条记录包含多个列（只包含一个列就成了标量子查询）
比如SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1)
列子查询
查询出一个列的数据，这个列的数据包含多条记录
SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2)
表子查询
子查询的结果包含很多条记录，又包含很多个列
比如SELECT * FROM t1 WHERE (m1, n1) IN (SELECT m2, n2 FROM t2)
按与外层查询关系区分子查询
相关子查询
子查询可以单独运行出结果，不依赖外层查询
不相关子查询
子查询的执行依赖于外层查询的值
SELECT * FROM t1 WHERE m1 IN (SELECT m2 FROM t2 WHERE n1 = n2)
子查询在布尔表示式中的使用
- 使用=, >, <, >=, <=, <>, !=, <=>
子查询只能是标量子查询或者行子查询，也就是子查询的结果只能返回单一的值或者一条记录
标量子查询SELECT * FROM t1 WHERE m1 < (SELECT MIN(m2) FROM t2);
行子查询SELECT * FROM t1 WHERE (m1, n1) = (SELECT m2, n2 FROM t2 LIMIT 1)
- [NOT] IN/ANY/SOME/ALL 子查询
对于列子查询和表子查询来说，他们的结果集中包含很多条记录，类似一个集合，就不能使用上面的操作符了。
- IN或者NOT IN
具体语法操作数 [NOT] IN （子查询）
判断某个操作数是否存在由子查询结果组成的集合中
比如SELECT * FROM t1 WHERE (m1, n2) IN (SELECT m2, n2 FROM t2);
- ANY/SOME（any和some是同义词）
具体语法操作数 操作符 ANY/SOME (子查询）
意思是：只要子查询的结果集合中有一条记录能满足比较的结果为真，则整个表达式的结果为真
SELECT * FROM t1 WHERE m1 > ANY(SELECT m2 FROM t2)
等价于SELECT * FROM t1 WHERE m1 > (SELECT MIN(m2) FROM t2);
- ALL
与ANY/SOME类似
SELECT * FROM t1 WHERE m1 > ALL(SELECT m2 FROM t2)
等价于SELECT * FROM t1 WHERE m1 > (SELECT MAX(m2) FROM t2)
- EXISTS子查询
有时候我们仅仅需要判断子查询的结果集中是否有记录，而不在乎他的记录具体是个啥，乐意使用EXISTS或者NOT EXISTS放在子查询语句的前边
[NOT] EXISTS (子查询)
比如SELECT * FROM t1 WHERE EXISTS (SELECT 1 FROM t2)
不关心子查询的结果是什么，只在乎子查询有没有记录
子查询注意事项
- 子查询必须用小括号扩起来
- SELECT子句中的子查询必须是标量子查询
- 想要得到标量子查询或者行子查询，但又不能保证子查询的结果集只有一条记录时，应该使用
LIMIT 1来限制记录数量 - [NOT] IN/ANY/SOME/ALL子查询不允许子句中有LIMIT
- 不允许增删改某个表同时还对该表进行子查询
原理
标量子查询、行子查询的执行方式
不相关子查询
SELECT * FROM s1 WHERE key1 = (SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1
先单独执行(SELECT common_field FROM s2 WHERE key3 = 'a' LIMIT 1这个子查询，
然后将上一步子查询得到的结果当做外层查询的查询语句
也就是说，MySQL对于包含不相关的标量子查询或者行子查询的查询语句来说，MySQL会分别执行外层查询和子查询，就当做两个单表查询就好了
相关子查询
比如SELECT * FROM s1 WHERE key1 = (SELECT common_field FROM s2 WHERE s1.key3 = s3.key3 LIMIT 1)
先从外层查询中获取一条记录，本例中也就是s1表
然后执行子查询；如果满足子查询，则将记录添加到结果集，否则丢弃。
依次递推，知道遍历完外层表中的所有记录
IN子查询的优化
SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a')
物化表的提出
不相关子查询的结果集写入一个临时表
- 该临时表的列就是子查询结果集中的列
- 写入临时表的记录会被去重
- 使用memory存储引擎的临时表，而且会为该表简历哈希索引
物化表转连接
从表1的角度看
从表2的角度看
总的来看，上面的含子查询的语句就转化成了
SELECT s1.* FROM s1 INNER JOIN materialized_table ON key1 = m_val
将子查询转换为semi-join
对于MySQL对外提供的接口不存在semi-join， 只是一种概念
不进行物化操作直接把子查询转化为连接
SELECT * FROM s1 WHERE key1 IN (SELECT common_field FROM s2 WHERE key3 = 'a')
类似于
SELECT s1.* FROM s1 INNER JOIN s2 ON s1.key1 = s2.common_field WHERE s2.key3 = 'a'
但是需要对结果去重，一个s1中的记录可以满足多条s2表中的记录
如何实现这种semi-join
- table pullout（子查询中的表上拉）
当子查询的查询列表处只有主键或者唯一索引列时，可以直接把子查询中的上拉到外层查询的FROM子句中，并把子查询中的搜索条件合并到外层查询的搜索条件中
- 重复值消除
执行连接查询时，消除重复的值。比如将结果集中的记录的主键插入到一个表中，插入失败丢弃该条记录
- 首次匹配
先取一条外层查询的记录，如果能匹配到一条子查询中的记录，就直接返回