子查询分类
子查询可以依据是否执行多次划分为关联子查询和非关联子查询。
子查询从表中查询了数据结果,如果这个数据结果只执行一次,然后这个数据结果作为主查询的条件执行,那么这样子查询叫做非关联子查询;如果这个子查询需要执行多次,即采用循环的方式,先从外部查询开始,每次都传入子查询进行查询,然后将结果反馈给外部,这种嵌套的执行方式称为关联子查询。
非关联子查询举例:
查询哪个球员身高最高、最高身高是多少?
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
(本文完)
- 我的微信
- 微信扫一扫
-
- 微信公众号
- 微信公众号扫一扫
-
请博主喝82年的敌敌畏~
支付宝扫一扫赞助
微信钱包扫描赞助
赏