SQL从入门到放弃系列——子查询[八]

2019年7月1日12:04:35 发表评论 28

摘要

子查询种类有哪些？
如何提高子查询性能？
SQL脚本（https://github.com/cystanford/sql_nba_data）

子查询分类

子查询可以依据是否执行多次划分为关联子查询和非关联子查询。

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

非关联子查询举例：

查询哪个球员身高最高、最高身高是多少？

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)

疑问，下面这句结果和上面一样，难道说上面仅仅是为了演示关联子查询？

SELECT player_name, height, team_id from player where height > (SELECT avg(height) from player)

EXISTS子查询

该子查询用来判断条件是否满足，满足为true，不满足为false。

比如查询出场过的球员的姓名、id、球队id。（是否出场由player_score表中是否有记录判断）

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)

集合比较子查询

集合比较子查询作用是和另一个查询结果集进行比较，用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)

这里还是有相同的疑问，为啥要加上WHERE player.player_id = player_score.player_id这个条件？

用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)

这要涉及到IN和EXISTS的执行过程了。

如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用IN；

如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。

区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键)，如果是EXISTS，那么以外层表为驱动表，先被访问；如果是IN，那么先执行子查询。所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了，另外IN时不对NULL进行处理。

IN 是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的，需要分情况。

NOT IN 和NOT EXISTS

如果查询语句使用了NOT IN那么内外表都进行全表扫描，没有用到索引；而NOT EXISTS的子查询依然能用到表上的索引。所以无论哪个表大，用NOT EXISTS都比NOT IN要快。

（本段摘自：https://www.cnblogs.com/liyasong/p/sql_in_exists.html）

ANY、ALL子查询

查询球员信息表中比team_id为1002中任何一个球员身高高的球员信息，输出id，name，height。

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

输出结果有35条。

查询比所有球员身高高的球员信息，输出id，name，height。

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

输出结果只有一条。

看上面两个需求的中文描述都一样，为啥结果不一样？

第一个其实是查询比1002球队中球员身高最低的高的结果；第二个其实是查询比1002球队中球员身高最高的都高的结果。

注意ANY、ALL关键字必须与比较操作符一起使用。

将子查询作为计算字段

比如：查询每个球队的球员数。

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

思考

查询场均得分大于20分的球员信息（id，name，team_id），场均得分从player_score表中查询。【仅供参考】。

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 AND player_score.score > 20)

或

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 AND player