DQL（数据查询语言）实战 - 子查询 - 《关系型数据库实战》

概述
关联子查询 & 非关联子查询
EXISTS 子查询
集合比较子查询
子查询作为计算字段

概述

SQL 中的查询其实是支持嵌套的。
嵌套在查询中的查询就称之为子查询，好处是可以让我们进行更复杂的查询，同时更加容易理解查询的过程。
因为很多时候，我们无法直接从数据表中得到查询结果，需要基于查询结果集再次进行查询，才能得到想要的结果。

关联子查询 & 非关联子查询

子查询虽然是一种嵌套查询的形式，不过我们依然可以依据子查询是否执行多次，从而将子查询划分为关联子查询和非关联子查询。

关联子查询：从数据表中查询了数据结果，如果这个数据结果只执行一次，然后这个数据结果作为主查询的条件进行执行，那么就是关联子查询。
非关联子查询：如果子查询需要执行多次，即采用循环的方式，先从外部查询开始，每次都传入子查询进行查询，然后再将结果反馈给外部，这种嵌套的执行方式就称为关联子查询。

下面，我们以 NBA 球员数据库为例进行演示，其中 NBA 球员数据可以从 https://github.com/cystanford/sql_nba_data 下载。
其中，一共包含了 5 张表：

player 表为球员表；
team 为球队表；
team_score 为球队比赛表；
player_score 为球员比赛成绩表；
height_grades 为球员身高对应的等级表。

我们以 NBA 球员数据表为例，假设我们想要知道哪个球员的身高最高，最高身高是多少，就可以采用子查询的方式：

SELECT player_name, height
  FROM player
  WHERE height = (SELECT max(height) FROM player)

可以看到，这个过程可以分为两个步骤：

通过SELECT max(height) FROM player可以得到最高身高这个数值，结果为 2.16；
再通过 player 这个表，看谁具有这个身高，再进行输出，这样的子查询就是非关联子查询。

如果子查询的执行依赖于外部查询，通常情况下都是因为子查询中的表用到了外部的表，并进行了条件关联，因此每执行一次外部查询，子查询都要重新计算一次，这样的子查询就称之为关联子查询。
比如我们想要查找每个球队中大于该球队平均身高的球员有哪些，并显示他们的球员姓名、身高以及所在球队 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
  );

这里我们将 player 表复制成了表 a 和表 b，每次计算的时候，需要将表 a 中的 team_id 传入从句，作为已知值。因为每次表 a 中的 team_id 可能是不同的，所以是关联子查询。

EXISTS 子查询

关联子查询通常也会和 EXISTS 一起来使用，EXISTS 子查询用来判断条件是否满足，满足的话为 True，不满足为 False。
我们来看一个示例，我们想要看出场过的球员都有哪些，并且显示他们的姓名、球员 ID 和球队 ID。其中，是否出场是通过 player_score 这张表中的球员出场表现来统计的，如果某个球员在 player_score 中有出场记录则代表他出场过，这里就使用到了 EXISTS 子查询。
查询语法如下：

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
  )

同样，NOT EXISTS 就是不存在的意思，我们也可以通过 NOT EXISTS 查询不存在于 player_score 表中的球员信息。

集合比较子查询

集合比较子查询的作用是与另一个查询结果集进行比较，我们可以在子查询中使用 IN、ANY、ALL 和 SOME 操作符，它们的含义和英文意义一样：

假设我们还是想要看出场过的球员都有哪些，可以采用 IN 子查询来进行操作：

SELECT
  player_id, team_id, player_name
  FROM player
  WHERE player_id IN (
    SELECT
      player_id
      FROM player_score
  )

你会发现运行结果和上面的是一样的，那么问题来了，既然 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)

实际上，哪种查询方式更高效取决于表A和表B的规模。

如果表 A 比表 B 大，那么 IN 子查询的效率要比 EXIST 子查询效率高，因为这时 B 表中如果对 cc 列进行了索引，那么 IN 子查询的效率就会比较高。
同样，如果表 A 比表 B 小，那么使用 EXISTS 子查询效率会更高，因为我们可以使用到 A 表中对 cc 列的索引，而不用从 B 中进行 cc 列的查询。

下面，我们来看下 ANY 和 ALL 子查询，ANY 和 ALL 都需要使用比较符，比较符包括了（>）（=）（<）（>=）（<=）和（<>）等。
如果我们想要查询球员表中，比印第安纳步行者（对应的 team_id 为 1002）中任意一个球员身高高的球员信息，并且输出他们的球员 ID、球员姓名和球员身高：

SELECT
  player_id, player_name, height
  FROM player
  WHERE height > ANY (SELECT height FROM player WHERE team_id = 1002)

同样，如果我们想要知道比印第安纳步行者（对应的 team_id 为 1002）中所有球员身高都高的球员的信息，那就需要改成 ALL：

SELECT
  player_id, player_name, height
  FROM player
  WHERE height > ALL (SELECT height FROM player WHERE team_id = 1002)

子查询作为计算字段

子查询结果除了可以用于条件过滤外，实际上子查询也可以作为主查询的计算字段。
比如我想查询每个球队的球员数，也就是对应 team 这张表，我需要查询相同的 team_id 在 player 这张表中所有的球员数量是多少：

SELECT
  team_name,
  (SELECT count(*) FROM player WHERE player.team_id = team.team_id) AS player_num
  FROM team;