子查询：关联查询和非关联查询

子查询是「查询」中的「查询」，就是「嵌套查询」。

以 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