MySQL之子查询

MySQL可以执行子查询，也就是在查询中嵌套查询

子查询分为两类：关联子查询和非关联子查询

假设系统现在有两个表，分别为 user 和 order。

user 为用户表，有id，name两个字段
order为订单表，有oid，userid两个字段。

我们现在需要求出所有买过东西的用户的name。

非关联子查询

非关联子查询 指的是 子查询 的查询条件 不依赖于 外部查询的数据

select name from user where user.id in (select userid from order);

上面的代码可以查出 所有买过东西的用户的name。
子查询中没有用到 外部 查询的数据，属于非关联子查询。

非关联子查询的流程如下：
1、执行子查询，得到order中对应的所有userid。
2、遍历order中得到的userid，到user中执行外部查询，也就是执行select name from user where user.id = userid.如果能查出对应的name，就将name返回给客户端

关联子查询

关联子查询 指的是 子查询 的查询条件 依赖于 外部查询的数据

select name from user exists(select * from order where order.userid = user.id)

上面的代码可以查出 所有买过东西的用户的name。
子查询中需要用到 外部 查询的数据 user.id ，属于非关联子查询。

关联子查询的流程如下：
1、执行外部查询，一条一条的取出user中的记录，拿出其中的id
2、根据记录中的id，执行内部查询，select * from order where order.userid = id。如果能查出对应的记录，就将对应的name返回给客户端。

关联子查询和非关联子查询的选择

关联子查询和非关联子查询 哪个 效率更高呢？

select name from user where user.id in (select userid from order);

select name from user exists(select * from order where order.userid = user.id)

1、user的id有索引，order的userid无索引
in 非关联子查询效率高，因为可以使用user的id索引

如果外部查询有索引，但是内存查询没有索引，最好采用 非关联查询

2、user的id无索引，order的userid有索引
exists 关联子查询效率高，可以使用order的userid索引

如果外部查询无索引，但是内存查询有索引，最好采用 关联查询

3、user的id有索引，order的userid有索引
这个时候需要比较 user 和 order的规模，
如果user表较大，in 非关联查询效率高，因为二者都有索引，但是in 非关联查询 是 将 order表中全表扫描，然后到user表中利用索引直接查询得到对应的结果，不用对user全盘扫描。这时只需要扫描 order表中的少数的记录。

如果采用 exists的话， 需要对 user表 全表扫描，然后到 order表中利用索引查询，不需要对order全盘扫描，但是这个时候需要扫描 user表中的多数的记录。

所以当外部查询的数据多时，最好采用 非关联查询

所以当外部查询的数据少时，最好采用 关联查询

3、user的id无索引，order的userid无索引

非关联查询和关联查询都一样，都需要全盘扫描，效率差不多。

子查询集合的比较

MySQL中提供了三个关键字来让一个元素和一个子查询集合进行比较

in
一个元素是否存在于另一个集合中

any
一个元素和集合中的任意一个元素比较

all
一个元素和集合中的所有元素比较

一个复杂的关联子查询例子

eg、 查询选取了所有课的学生的学号

该sql的执行流程如下：

这个sql有两个exists子查询，我们将第一个子查询叫做外部子查询，将第二个子查询叫做内部子查询

1、对student表做扫描，逐行的取出一行记录，这里的记录必须满足除了exists的其他所有where条件。

2、将student中的一条记录送入到外部关联子查询中。

3、course也有一个exist条件，出现套娃了。所以也要对course表做扫描，逐行的取出一条记录，这里的记录必须满足除了exists的其他所有的where条件。

4、将student和course的一行记录送入到内部关联子查询中。也就是select * S_C where S_C.SNO = S.SNO and S_C.CNO = C.CNO。
可以通过对应的索引来加快这个查询语句。

5、如果S_C中存在对应的记录，not_exists会返回false，如果S_C中不存在对应的记录，为null，not_exists会返回true。

当返回true的时候，MySQL会将对应的记录保存起来。

6、由于是select * from course，MySQL会继续扫描下一行的course记录，student记录还是之前的那个记录，然后继续查询S_C。

7、当所有的course都查询完毕后，如果内部子查询对应的结果集中不为null，第一层的not exists就会返回一个false，MySQL就会将这个student记录给抛弃，说明记录不符合条件。
如果内部子查询的结果为null，那么就将对应的student返回给客户端，说明记录符合条件。

8、继续从student中扫描下一条记录，执行对应的逻辑，将所有符合条件的student返回给客户端。

通过这个逻辑我们就可以分析出为什么这条sql能查询出选择了所有课的学生了
。

如果一个学生选择了所有课，那么对应的内部子查询的结果集为null，外部的not exists会判断为true，MySQL会将对应的记录返回给客户端。

如果一个学生一旦少选了一门课，对应的内部子查询的结果集都是不为null的，那么外部的not exists就会为false，MySQL不会将对应的记录返回给客户端。