什么是关联子查询,什么是非关联子查询
依据子查询是否执行多次,从而将子查询划分为关联子查询和非关联子查询。
**
- 子查询从数据表中查询了数据结果,如果这个数据结果只执行一次,然后这个数据结果作为主查询的条件进行执行,那么这样的子查询叫做非关联子查询。
- 如果子查询需要执行多次,即采用循环的方式,先从外部查询开始,每次都传入子查询进行查询,然后再将结果反馈给外部,这种嵌套的执行方式就称为关联子查询。
想要知道哪个球员的身高最高,最高身高是多少,就可以采用子查询的方式:
- 非关联
SELECT player_name, height FROM player WHERE height = (SELECT max(height) FROM player)
查找每个球队中大于平均身高的球员有哪些,并显示他们的球员姓名、身高以及所在球队 ID:
- 关联
- 通常情况下都是因为子查询中的表用到了外部的表,并进行了条件关联,因此每执行一次外部查询,子查询都要重新计算一次
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 子查询
关联子查询通常也会和 EXISTS 一起来使用,EXISTS 子查询用来判断条件是否满足,满足的话为 True,不满足为 False。
看出场过的球员都有哪些:
- 关联
- 子查询中确实用到了外部的 player 表
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)
可以通过 NOT EXISTS 查询不存在于 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)
集合比较子查询
集合比较子查询的作用是与另一个查询结果集进行比较:
- 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 还是 EXISTS 呢?
模式抽象:
# IN
SELECT * FROM A WHERE cc IN (SELECT cc FROM B)
# EXIST
SELECT * FROM A WHERE EXIST (SELECT cc FROM B WHERE B.cc=A.cc)
- IN, 用 B 表的索引, A 表大于 B 表时用
- EXIST, 用 A 表的索引, B 表大于 A 表时用
问题在于 IN 和 EXIST 如何利用索引.
ANY 和 ALL
ANY 和 ALL 都需要使用比较符,比较符包括了(>)(=)(<)(>=)(<=)和(<>)等.
要查询球员表中,比印第安纳步行者(对应的 team_id 为 1002)中任意一个球员身高高的球员信息,并且输出他们的球员 ID、球员姓名和球员身高,该怎么写呢?
只要高于某一个球员就行.
SELECT player_id, player_name, height FROM player WHERE height > ANY (SELECT height FROM player WHERE team_id = 1002)
要知道比印第安纳步行者(对应的 team_id 为 1002)中所有球员身高都高的球员的信息,并且输出球员 ID、球员姓名和球员身高,该怎么写呢?
要高于所有球员.
SELECT player_id, player_name, height 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
总结
精选留言
看,有只猪
IN表是外边和内表进行hash连接,是先执行子查询。
EXISTS是对外表进行循环,然后在内表进行查询。
因此如果外表数据量大,则用IN,如果外表数据量小,也用EXISTS。
IN有一个缺陷是不能判断NULL,因此如果字段存在NULL值,则会出现返回,因为最好使用NOT EXISTS。
作者回复: 总结的不错
Sam
not in是先执行子查询,得到一个结果集,将结果集代入外层谓词条件执行主查询,子查询只需要执行一次;
not exists是先从主查询中取得一条数据,再代入到子查询中,执行一次子查询,判断子查询是否能返回结果,主查询有多少条数据,子查询就要执行多少次。
作者回复: 总结的不错