极客时间已完结课程限时免费阅读

09丨子查询：子查询的种类都有哪些，如何提高子查询的性能？

09丨子查询：子查询的种类都有哪些，如何提高子查询的性能？-极客时间

09丨子查询：子查询的种类都有哪些，如何提高子查询的性能？

2019-07-01 陈旸 来自北京
《SQL必知必会》
课程介绍

讲述：陈旸

时长11:44大小10.71M

上节课我讲到了聚集函数，以及如何对数据进行分组统计，可以说我们之前讲的内容都是围绕单个表的 SELECT 查询展开的，实际上 SQL 还允许我们进行子查询，也就是嵌套在查询中的查询。这样做的好处是可以让我们进行更复杂的查询，同时更加容易理解查询的过程。因为很多时候，我们无法直接从数据表中得到查询结果，需要从查询结果集中再次进行查询，才能得到想要的结果。这个“查询结果集”就是今天我们要讲的子查询。
通过今天的文章，我希望你可以掌握以下的内容：
子查询可以分为关联子查询和非关联子查询。我会举一个 NBA 数据库查询的例子，告诉你什么是关联子查询，什么是非关联子查询；
子查询中有一些关键词，可以方便我们对子查询的结果进行比较。比如存在性检测子查询，也就是 EXISTS 子查询，以及集合比较子查询，其中集合比较子查询关键词有 IN、SOME、 ANY 和 ALL，这些关键词在子查询中的作用是什么；
子查询也可以作为主查询的列，我们如何使用子查询作为计算字段出现在 SELECT 查询中呢？

什么是关联子查询，什么是非关联子查询

子查询虽然是一种嵌套查询的形式，不过我们依然可以依据子查询是否执行多次，从而将子查询划分为关联子查询和非关联子查询。
子查询从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么这样的子查询叫做非关联子查询。
同样，如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为关联子查询。
单说概念有点抽象，我们用数据表举例说明一下。这里我创建了 NBA 球员数据库，SQL 文件你可以从GitHub上下载。
文件中一共包括了 5 张表，player 表为球员表，team 为球队表，team_score 为球队比赛表，player_score 为球员比赛成绩表，height_grades 为球员身高对应的等级表。
其中 player 表，也就是球员表，一共有 37 个球员，如下所示：
team 表为球队表，一共有 3 支球队，如下所示：
team_score 表为球队比赛成绩表，一共记录了两场比赛的成绩，如下所示：
player_score 表为球员比赛成绩表，记录了一场比赛中球员的表现。这张表一共包括 19 个字段，代表的含义如下：
其中 shoot_attempts 代表总出手的次数，它等于二分球出手和三分球出手次数的总和。比如 2019 年 4 月 1 日，韦恩·艾灵顿在底特律活塞和印第安纳步行者的比赛中，总出手次数为 19，总命中 10，三分球 13 投 4 中，罚球 4 罚 2 中，因此总分 score=(10-4)×2+4×3+2=26，也就是二分球得分 12+ 三分球得分 12+ 罚球得分 2=26。
需要说明的是，通常在工作中，数据表的字段比较多，一开始创建的时候会知道每个字段的定义，过了一段时间再回过头来看，对当初的定义就不那么确定了，容易混淆字段，解决这一问题最好的方式就是做个说明文档，用实例举例。
比如 shoot_attempts 是总出手次数（这里的总出手次数 = 二分球出手次数 + 三分球出手次数，不包括罚球的次数），用上面提到的韦恩·艾灵顿的例子做补充说明，再回过头来看这张表的时候，就可以很容易理解每个字段的定义了。
我们以 NBA 球员数据表为例，假设我们想要知道哪个球员的身高最高，最高身高是多少，就可以采用子查询的方式：
SQL: SELECT player_name, height FROM player WHERE height = (SELECT max(height) FROM player)
运行结果：（1 条记录）
你能看到，通过SELECT max(height) FROM player可以得到最高身高这个数值，结果为 2.16，然后我们再通过 player 这个表，看谁具有这个身高，再进行输出，这样的子查询就是非关联子查询。
如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。比如我们想要查找每个球队中大于平均身高的球员有哪些，并显示他们的球员姓名、身高以及所在球队 ID。
首先我们需要统计球队的平均身高，即SELECT avg(height) FROM player AS b WHERE a.team_id = b.team_id，然后筛选身高大于这个数值的球员姓名、身高和球队 ID，即：
SELECT player_name, height, team_id FROM player AS a WHERE height > (SELECT avg(height) FROM player AS b WHERE a.team_id = b.team_id)
运行结果：（18 条记录）
这里我们将 player 表复制成了表 a 和表 b，每次计算的时候，需要将表 a 中的 team_id 传入从句，作为已知值。因为每次表 a 中的 team_id 可能是不同的，所以是关联子查询。如果是非关联子查询，那么从句计算的结果是固定的才可以。

EXISTS 子查询

关联子查询通常也会和 EXISTS 一起来使用，EXISTS 子查询用来判断条件是否满足，满足的话为 True，不满足为 False。
比如我们想要看出场过的球员都有哪些，并且显示他们的姓名、球员 ID 和球队 ID。在这个统计中，是否出场是通过 player_score 这张表中的球员出场表现来统计的，如果某个球员在 player_score 中有出场记录则代表他出场过，这里就使用到了 EXISTS 子查询，即EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)，然后将它作为筛选的条件，实际上也是关联子查询，即：
SQL：SELECT player_id, team_id, player_name FROM player WHERE EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)
运行结果：（19 条记录）
同样，NOT EXISTS 就是不存在的意思，我们也可以通过 NOT EXISTS 查询不存在于 player_score 表中的球员信息，比如主表中的 player_id 不在子表 player_score 中，判断语句为NOT EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)。整体的 SQL 语句为：
SQL: SELECT player_id, team_id, player_name FROM player WHERE NOT EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)
运行结果：（18 条记录）

集合比较子查询

集合比较子查询的作用是与另一个查询结果集进行比较，我们可以在子查询中使用 IN、ANY、ALL 和 SOME 操作符，它们的含义和英文意义一样：
还是通过上面那个例子，假设我们想要看出场过的球员都有哪些，可以采用 IN 子查询来进行操作：
SELECT player_id, team_id, player_name FROM player WHERE player_id in (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id)
你会发现运行结果和上面的是一样的，那么问题来了，既然 IN 和 EXISTS 都可以得到相同的结果，那么我们该使用 IN 还是 EXISTS 呢？
我们可以把这个模式抽象为：
SELECT * FROM A WHERE cc IN (SELECT cc FROM B)
SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc)
实际上在查询过程中，在我们对 cc 列建立索引的情况下，我们还需要判断表 A 和表 B 的大小。在这里例子当中，表 A 指的是 player 表，表 B 指的是 player_score 表。如果表 A 比表 B 大，那么 IN 子查询的效率要比 EXIST 子查询效率高，因为这时 B 表中如果对 cc 列进行了索引，那么 IN 子查询的效率就会比较高。
同样，如果表 A 比表 B 小，那么使用 EXISTS 子查询效率会更高，因为我们可以使用到 A 表中对 cc 列的索引，而不用从 B 中进行 cc 列的查询。
了解了 IN 查询后，我们来看下 ANY 和 ALL 子查询。刚才讲到了 ANY 和 ALL 都需要使用比较符，比较符包括了（>）（=）（<）（>=）（<=）和（<>）等。
如果我们想要查询球员表中，比印第安纳步行者（对应的 team_id 为 1002）中任意一个球员身高高的球员信息，并且输出他们的球员 ID、球员姓名和球员身高，该怎么写呢？首先我们需要找出所有印第安纳步行者队中的球员身高，即SELECT height FROM player WHERE team_id = 1002，然后使用 ANY 子查询即：
SQL: SELECT player_id, player_name, height FROM player WHERE height > ANY (SELECT height FROM player WHERE team_id = 1002)
运行结果：（35 条记录）
运行结果为 35 条，你发现有 2 个人的身高是不如印第安纳步行者的所有球员的。
同样，如果我们想要知道比印第安纳步行者（对应的 team_id 为 1002）中所有球员身高都高的球员的信息，并且输出球员 ID、球员姓名和球员身高，该怎么写呢？
SQL: SELECT player_id, player_name, height FROM player WHERE height > ALL (SELECT height FROM player WHERE team_id = 1002)
运行结果：（1 条记录）
我们能看到比印第安纳步行者所有球员都高的球员，在 player 这张表（一共 37 个球员）中只有索恩·马克。
需要强调的是 ANY、ALL 关键字必须与一个比较操作符一起使用。因为如果你不使用比较操作符，就起不到集合比较的作用，那么使用 ANY 和 ALL 就没有任何意义。

将子查询作为计算字段

我刚才讲了子查询的几种用法，实际上子查询也可以作为主查询的计算字段。比如我想查询每个球队的球员数，也就是对应 team 这张表，我需要查询相同的 team_id 在 player 这张表中所有的球员数量是多少。
SQL: SELECT team_name, (SELECT count(*) FROM player WHERE player.team_id = team.team_id) AS player_num FROM team
运行结果：（3 条记录）
你能看到，在 player 表中只有底特律活塞和印第安纳步行者的球员数据，所以它们的 player_num 不为 0，而亚特兰大老鹰的 player_num 等于 0。在查询的时候，我将子查询SELECT count(*) FROM player WHERE player.team_id = team.team_id作为了计算字段，通常我们需要给这个计算字段起一个别名，这里我用的是 player_num，因为子查询的语句比较长，使用别名更容易理解。

总结

今天我讲解了子查询的使用，按照子查询执行的次数，我们可以将子查询分成关联子查询和非关联子查询，其中非关联子查询与主查询的执行无关，只需要执行一次即可，而关联子查询，则需要将主查询的字段值传入子查询中进行关联查询。
同时，在子查询中你可能会使用到 EXISTS、IN、ANY、ALL 和 SOME 等关键字。在某些情况下使用 EXISTS 和 IN 可以得到相同的效果，具体使用哪个执行效率更高，则需要看字段的索引情况以及表 A 和表 B 哪个表更大。同样，IN、ANY、ALL、SOME 这些关键字是用于集合比较的，SOME 是 ANY 的别名，当我们使用 ANY 或 ALL 的时候，一定要使用比较操作符。
最后，我讲解了如何使用子查询作为计算字段，把子查询的结果作为主查询的列。
SQL 中，子查询的使用大大增强了 SELECT 查询的能力，因为很多时候查询需要从结果集中获取数据，或者需要从同一个表中先计算得出一个数据结果，然后与这个数据结果（可能是某个标量，也可能是某个集合）进行比较。
我今天讲解了子查询的使用，其中讲到了 EXISTS 和 IN 子查询效率的比较，当查询字段进行了索引时，主表 A 大于从表 B，使用 IN 子查询效率更高，相反主表 A 小于从表 B 时，使用 EXISTS 子查询效率更高，同样，如果使用 NOT IN 子查询和 NOT EXISTS 子查询，在什么情况下，哪个效率更高呢？
最后请你使用子查询，编写 SQL 语句，得到场均得分大于 20 的球员。场均得分从 player_score 表中获取，同时你需要输出球员的 ID、球员姓名以及所在球队的 ID 信息。
欢迎在评论区写下你的思考，也欢迎点击请朋友读把这篇文章分享给你的朋友或者同事。
分享给需要的人，Ta购买本课程，你将得20元
生成海报并分享

赞 26

提建议

© 版权归极客邦科技所有，未经许可不得传播售卖。 页面已增加防盗追踪，如有侵权极客邦将依法追究其法律责任。
上一篇
08丨什么是SQL的聚集函数，如何利用它们汇总表的数据？
下一篇
10丨常用的SQL标准有哪些，在SQL92中是如何使用连接的？
unpreview
 写留言

精选留言(192)

  • 看，有只猪
    2019-07-01
    IN表是外边和内表进行hash连接，是先执行子查询。 EXISTS是对外表进行循环，然后在内表进行查询。 因此如果外表数据量大，则用IN，如果外表数据量小，也用EXISTS。 IN有一个缺陷是不能判断NULL，因此如果字段存在NULL值，则会出现返回，因为最好使用NOT EXISTS。

    作者回复: 总结的不错

    共 11 条评论
    171
  • humor
    2019-07-03
    exist和in的区别这一块，感觉没有讲清楚呀，我听下来还是不太明白哪种情况应该用in，哪种情况应该用exists。选择的标准是看能否使用表的索引吗？

    作者回复: 索引是个前提，其实和选择与否 还是要看表的大小。选择的标准，你可以理解为： 小表驱动大表。这种方式下效率是最高的。比如 SELECT * FROM A WHERE cc IN (SELECT cc FROM B) SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc) 当A小于B时，用EXIST。因为EXIST的实现，相当于外表循环，实现的逻辑类似于： for i in A for j in B if j.cc == i.cc then ... 当B小于A时，用IN，因为实现的逻辑类似于： for i in B for j in A if j.cc == i.cc then ... 所以哪个表小就用哪个表来驱动，A表小 就用EXIST，B表小 就用IN

    共 19 条评论
    88
  • Sam
    2019-07-01
    not in是先执行子查询，得到一个结果集，将结果集代入外层谓词条件执行主查询，子查询只需要执行一次； not exists是先从主查询中取得一条数据，再代入到子查询中，执行一次子查询，判断子查询是否能返回结果，主查询有多少条数据，子查询就要执行多少次。

    作者回复: 总结的不错

    共 2 条评论
    54
  • hsj
    2019-09-29
    SELECT * FROM A WHERE cc IN (SELECT cc FROM B) SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc) A 表有 n 条数据，B 表有 m 条数据 两条语句的执行效率（假设都用 btree 索引） 用 in ：m * log (n) 用 exists： n * log (m) 所以小表驱动大表能够大幅度提高执行速度
    展开

    作者回复: 总结的不错！

    共 7 条评论
    36
  • Hero
    2019-07-01
    1. SELECT player_id, team_id, player_name FROM player a WHERE EXISTS ( SELECT b.player_id FROM player_score b GROUP BY b.player_id HAVING AVG( b.score ) > 20 and a.player_id = b.player_id); 2.SELECT player_id, team_id, player_name FROM player a WHERE EXISTS ( SELECT b.player_id FROM player_score b WHERE a.player_id = b.player_id GROUP BY b.player_id HAVING AVG( b.score ) > 20); 3.SELECT player_id, team_id, player_name FROM player WHERE player_id IN ( SELECT player_id FROM player_score GROUP BY player_id HAVING AVG( score ) > 20 ); 推荐3，因为子查询只会执行一次。2比1好，因为where会先过滤数据行，然后分组，然后对分组过滤。
    展开

    作者回复: Good Job 总结的不错

    共 9 条评论
    32
  • Serendipity
    2019-09-01
    我怎么感觉简单的地方讲的比较多，难的地方一笔带过了？看的好懵逼。

    作者回复: 后面有些内容是和SQL优化相关的，前面相对基础

    共 5 条评论
    27
  • Chuan
    2019-11-01
    老师，听完这节课，有几个问题，烦请解惑： 1. in和子查询结合使用时，子查询中列的索引是否能使用？exist子查询呢？ 2. 如果针对某个字段（列）添加索引，那么在查询这个字段及内容时，时间复杂度是否从O(n)变为O(1)或者O(logn)？ 3. 回复中，您关于in和exist的解释，还是有点不理解。查询资料说： 两者除了驱动关系不同，in会缓存结果，但是没法使用索引；而exist不会缓存结果，每次会查数据库，但是不关心内容，只关心是否存在行记录。不知道您看法如何？ 4. 您回复中，两个都是两层for循环，使得读者理解起来很困难。我觉得是不是可以这么理解： in的子查询中不能使用索引，在获得结果时耗时较久，接近O(n)，所以适合内表小的场景，因为外表大但可以使用索引；exist子查询可以使用索引，同时外表都需要全遍历，所以适合内表大的场景。不过这个理解好像建立在问题1，2的答案之上。 感觉读者中不理解的蛮多的，网上资料基本上也说得不清不楚，望老师解惑。
    展开

    作者回复: IN子查询，是从表计算出来作为已知值，而EXISTS子查询是主表作为已知值传递给从表。 基于小表驱动大表的原则，主表A小 就用EXIST，从表B表小 就用IN 这里主要看能否用到索引，假设主表A有n条数据，从表B有m条数据，表A和表B中的查询字段采用B+树进行索引，那么两个子查询的执行效率： 使用 in ：log (n) * m 使用 exists： n * log (m) 对于IN子查询，计算出来的结果作为已知值，就可以使得表A（n条数据）可以使用到索引，从而提升检索效率 对于EXISTS子查询，外表A作为已知值，传递给从表，可以利用从表B（m条数据）中的索引，从而提升检索效率。 （说明log 代表以2为底的对数，即B+树的深度）

    共 2 条评论
    13
  • OperaX
    2019-07-07
    SELECT t1.player_id, t1.player_name, t1.team_id FROM player t1 WHERE t1.player_id IN ( SELECT t2.player_id FROM player_score t2 GROUP BY t2.player_id HAVING ( AVG( t2.score ) > 20 ) )
    展开

    作者回复: 正确，而且SQL代码结构清晰

    共 2 条评论
    12
  • Goal
    2019-07-01
    # 使用exists SELECT player_name,player_id,team_id from player WHERE EXISTS (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id and score >20) # 使用 in SELECT player_name,player_id,team_id from player WHERE player_id IN (SELECT player_id FROM player_score WHERE score >20) 使用：join 语句 SELECT p.player_name,p.player_id,p.team_id,t.score from player AS p JOIN player_score as t WHERE p.player_id = t.player_id and t.score >20
    展开
    共 4 条评论
    10
  • 小星星
    2019-07-09
    SELECT player_name, height, team_id FROM player AS a WHERE height > (SELECT avg(height) FROM player AS b WHERE a.team_id = b.team_id); SELECT player_name, height, team_id FROM player WHERE height > (SELECT avg(height) FROM player as b); 这两句执行的结果一样的；请问为什么需要加上a.team_id = b.team_id 有什么区别吗？
    展开

    作者回复: 第一个SQL 18条记录 第二个SQL 19条记录

    共 4 条评论
    8
  • 华夏
    2019-07-01
    SELECT player_id, team_id, player_name FROM player WHERE player_id in (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id); 老师，文稿中这句的WHERE player.player_id = player_score.player_id可以不要了哈。
    6
  • 佚花
    2019-07-01
    这么说吧，我在银行遇到过两张表1700w的表，我要对比他们的差值。用in没查出来，就是查很久还报错了，用了exsit用了40s 一张1700w，一张100个值，in是一分钟，exsit是0.1s

    作者回复: 佚花同学很好的分享

    共 2 条评论
    6
  • Liam
    2019-07-01
    老师您好，关于exist和in的差别，总结和举例的不符吧？主表大于从表不应该是使用in更好吗

    作者回复: 当主表A大于从表B时，是使用IN子查询更好

    共 5 条评论
    6
  • OperaX
    2019-07-07
    实际工作中没用到果any all。什么样的情况下会可能用到呢？老师举的例子的话，直接比较最大值或者最小值就好了吧

    作者回复: any all 通常是可以比较最大值或者最小值，两种不同的写法

    5
  • Mr.H
    2019-07-01
    最后的总结in和exist写反了吧

    作者回复: 当主表A大于从表B时，使用IN子查询效率更高，相反主表A小于从表B时，使用EXISTS子查询效率更高

    5
  • 张驰皓
    2019-12-21
    SELECT player_id, team_id, player_name FROM player WHERE player_id in (SELECT player_id FROM player_score WHERE player.player_id = player_score.player_id) 这段代码里，子查询里 where 部分删去后运行结果与此前一致，是否说明此 where 语句是无意义的？
    展开

    作者回复: 是因为这些player在player_score中都有记录，player这张表的数据量有些少，如果数据量大的话，没有在player_score（即没有打过比赛的球员）是不会显示出来的

    4
  • 峻铭
    2019-09-03
    前提条件： 1、我理解的场均得分大于20，第一场得了40分，第二场得了2分，场均为21分满足条件 2、一场比赛中，球员可以出现多次 解析思路，先得出球员在每场比赛中的总分数，然后对球员分组，计算球员在所参加比赛中的平均数 SELECT t2.player_id, t2.player_name, t2.team_id, t3.v FROM player AS t2 JOIN ( SELECT t1.player_id, avg( t1.total ) AS v FROM ( SELECT player_id, sum( score ) AS total FROM player_score WHERE game_id IN ( SELECT game_id FROM player_score GROUP BY game_id ) GROUP BY player_id #对球员分组，计算在每场比赛中的总分数 ) AS t1 GROUP BY t1.player_id #对球员分组，计算所参加比赛的平均数 HAVING v > 20 #场均得分大于20 ) AS t3 ON t2.player_id = t3.player_id; +-----------+------------------+---------+---------+ | player_id | player_name | team_id | v | +-----------+------------------+---------+---------+ | 10001 | 韦恩-艾灵顿 | 1001 | 26.0000 | | 10002 | 雷吉-杰克逊 | 1001 | 22.0000 | +-----------+------------------+---------+---------+
    展开

    作者回复: 整理和分析的不错

    共 3 条评论
    3
  • 华夏
    2019-07-01
    SELECT player_id, player_name, team_id FROM player AS a WHERE (SELECT score FROM player_score AS b WHERE a.player_id = b.player_id) > 20; +-----------+------------------+---------+ | player_id | player_name | team_id | +-----------+------------------+---------+ | 10001 | 韦恩-艾灵顿 | 1001 | | 10002 | 雷吉-杰克逊 | 1001 | +-----------+------------------+---------+ 2 rows in set (0.01 sec)
    展开

    作者回复: 需要注意下是场均得分>20

    3
  • Cary
    2019-07-01
    in 和 exist 上面和最后总结的好像不一致
    3
  • 苏籍
    2019-07-02
    WHERE a.team_id = b.team_id 想确定一下这个条件 是在两个相同的表做笛卡尔积后 做了啥操作，能帮梳理一下思路吗

    作者回复: for player1 in a表 for player2 in b表 根据player2.team_id=player1.team_id 进行筛选，然后求解avg(player2.height)

    共 2 条评论
    2