oracle中 常用的 join on 相关和 集合运算的总结

时间:2023-03-09 15:40:23
oracle中 常用的 join on 相关和 集合运算的总结

sql常用联合查询的 join on 、 left join(左连接) 、 right join (右连接)、inner join (等值连接)以及常用的集合运算有:union、unionall、minus、intersect的效果和总结。

若有人问我用select * from a,b where a.id=b.id;这种基础的语法就能完成我想要的结果,为什么用join等语法呢,答案是:这样做,极大的提高的查询效率。

首先接着用上一篇的book表和pbook表:
oracle中 常用的 join on 相关和 集合运算的总结oracle中 常用的 join on 相关和 集合运算的总结

首先把join on和inner join 放在一起:

select  * from   book a    join  ( select id,name,price from   pbook) b on   a.id=b.id;
select * from book a inner join ( select id,name,price from pbook) b on a.id=b.id;

oracle中 常用的 join on 相关和 集合运算的总结oracle中 常用的 join on 相关和 集合运算的总结

相比较这结果一模一样,只返回两个表中联结字段id相等的行 ,所以想  可以理解为  join on  与inner join on 相同

接着我们看左、右连接比较:

select  * from   book a    left join  ( select id,name,price from   pbook) b on   a.id=b.id;
select * from book a right join ( select id,name,price from pbook) b on a.id=b.id;

oracle中 常用的 join on 相关和 集合运算的总结oracle中 常用的 join on 相关和 集合运算的总结

明显能看出,左连接以左表为主,左表全部显示,右表只显示关联的,其余为空,总行数是左表的行。右连接是以右表为主,显示右边所有行,左表关联的行显示,其余为空

补充说明以及应用推广:

假设 三个或者4个表作为连接,分组 查询a表的某个字段,b表的某个字段,c表的某个字段,且根据一定的条件作为约束

select A.f_cjdwBH AS f_cjdwBH,
A.F_CJDWMC AS F_CJDWMC,
SUM(F_YCLYCZ_JZ) F_ZRCL,
SUM(B.F_YCZ) F_YCZ
from XY_CLYC_DJZRCLYC A
left JOIN XY_CLYC_QKZRCLYC B
ON A.F_QKMC = B.F_QKMC
AND A.F_NY = B.F_NY
left Join XY_CLYC_DWYCZRCL c
on a.f_qkbh=c.F_MCBH
and a.f_ny=c.f_qj left join XY_XYZD_QKXYCJDW d
on a.f_qkmc=d.f_qkmc
and d.f_nd= '' WHERE A.F_QKMC='无人区'
AND A.F_YCKSSJ = ''
AND A.F_YCJSSJ = ''
AND A.F_NY >= ''
AND A.F_NY <= '' GROUP BY A.F_CJDWBH, A.F_CJDWMC

由此推广应用。 重点说明:join on 的on后面是可以跟and条件,但是在多表查询的时候,把筛选条件 最好写在最后的where 条件下面,不能跟在某一个on 中。on只应用于2个表的关系条件

下面 常用的集合运算的比较:

首先我们比较一下union 和union all 结果比较

select   * from  book      union  select id,name,price from   pbook;
select * from book union all select id,name,price from pbook;

oracle中 常用的 join on 相关和 集合运算的总结oracle中 常用的 join on 相关和 集合运算的总结

这里能够看出:union 和union all 把相同列合并了,union对合并的数据去掉了重复行并且进行了排序。而union all 则是把2个表合起来,没有排序或者去重。

提示:1.表面上看union对数据进行排序,但是不能保证排序一定正确。(在oralce10之前是排序去重,之后是Hash UNIQUE运算去重,而它只比较散列值不进行排序)

           2.当数据量很大时,速率上来讲,union all 会比union 快很多。

提示补充:union 和union all 不会对比你的2个表的字段是否相等进行匹配,只对比要合并的2个表 是否有相等数量的字段列,且字段列类型相等,合并之后以左表字段为主。

比如 a,b表: a表3列数据,id,username,age ;b表数据如下:id,guojia,rq  最后的结果都是以a表为主显示的列。

oracle中 常用的 join on 相关和 集合运算的总结oracle中 常用的 join on 相关和 集合运算的总结  合并之后:oracle中 常用的 join on 相关和 集合运算的总结oracle中 常用的 join on 相关和 集合运算的总结

下一组比较:minus,intersect

select   * from  book      minus select id,name,price from   pbook;
select * from book intersect (select id,name,price from pbook)

oracle中 常用的 join on 相关和 集合运算的总结oracle中 常用的 join on 相关和 集合运算的总结

很明显:minus是把2个表冲突数据提出来,而intersect是把2个表的相同数据提出来

本文为博主原创,如有转载,请标记文章出处:http://www.cnblogs.com/mobeisanghai/p/7544544.html
 作者:漠北桑海