子查询：关联查询和非关联查询
子查询是「查询」中的「查询」，就是「嵌套查询」。
以 NBA 的SQL数据库文件为例。
检索问题：谁是数据库里身高最高的球员？
SELECT player_name, height
FROM player
WHERE height = (SELECT max(height) FROM player);
关联查询
关联查询：对外部的表进行了条件关联。
检索问题：查询每支球队中，身高大于球队平均身高的球员？
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);
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);
通过判断某个球员是否出现在 player_score 表中，得知该名球员是否出场。
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);
反过来，如果主表的 player_id不在 player_score 上，就是 NOT EXIST，可以查询未出场的球员
子查询中的「集合比较」
集合比较的操作符关键词与其英文含义一样，见下：
| 操作符 | 说明 |
|---|---|
| IN | 判断，是否在集合中 |
| ANY | 与比较操作符一起使用，去子查询返回的任何值做比较 |
| ALL | 与比较操作符一起使用，去子查询返回的所有值做比较 |
| SOME | 其实，它是 ANY 的别名 |
同样的查询目的，查看出场球员名单，用 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 替代 EXIST，查询结果一致。
SELECT * FROM A WHERE cc IN (SELECT cc FROM B)
SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc)
在这里，EXIST比IN查询效率更高，为什么？
因为表 player 小于 表 play_score。
在对 cc 列建索引时, 先判断表 A 和 B谁更大。如果A更大，B 中对列 cc 进行索引，IN 的效率更高。
SELECT player_id, player_name, height, team_id
FROM player
WHERE height > ALL (SELECT height FROM player WHERE team_id = 1002)
子查询：计算字段
SELECT team_name, (SELECT count(*)
FROM player
WHERE player.team_id = team.team_id) AS player_num
FROM team
这里，子查询得到的 count(*) 结果作为计算字段，起别名为 player_num
