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

2019年7月1日12:04:35 发表评论 83
摘要

子查询种类有哪些?
如何提高子查询性能?
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

(本文完)

  • 我的微信
  • 这是我的微信扫一扫
  • weinxin
  • 我的微信公众号
  • 我的微信公众号扫一扫
  • weinxin
  • A+
所属分类:SQL

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: