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表：

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

1 2	`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;`

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

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

1 2	`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;`

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

补充说明以及应用推广：

假设三个或者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= '2017'
  
WHERE  A.F_QKMC='无人区'
   AND A.F_YCKSSJ = '201701'
   AND A.F_YCJSSJ = '201712'
   AND A.F_NY >= '201701'
   AND A.F_NY <= '201712'

 GROUP BY A.F_CJDWBH, A.F_CJDWMC

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

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

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

1 2	`select` `` `from` `book` `union` 　`select` `id,name,price` `from` `pbook;` `select` `` `from` `book` `union` `all`　`select` `id,name,price` `from` `pbook;`

这里能够看出：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表为主显示的列。

合并之后：

下一组比较：minus，intersect

1 2	`select` `` `from` `book minus`　`select` `id,name,price` `from` `pbook;` `select` `` `from` `book` `intersect` `(select` `id,name,price` `from` `pbook)`