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不会将对应的记录返回给客户端。