left join、right join以及补全月份的实践总结——史上最详细的讲解

left join 和right join总结，以及补全月份的实践总结

 

平常一直在用关联，但是突然感觉自己并不是真正的理解，于是决定停下来整理一下。至于left join、join、right join和inner join等等各种join的区别。最常见的就是一张图解图，如下：

 

从上面这个图可以总结出来几句话：

left join:查出来的结果显示左边的所有数据，然后右边显示的是和左边有交集部分的数据

right join:查出来的结果显示右边的所有数据，然后左边显示的是和右边有交集部分的数据

inner join：查出来是两个表有交集的部分

其他的不做解释。

但是只是停留在这个理解上，在解决实际问题上可能会遇到瓶颈。常用的操作是用一张表关联其他的表（多为维度表），然后取我们想要的字段。从网上找的例子大多是学科和成绩，这里举的例子是月份、薪水和补贴。为什么举这个例子呢？为了方便后面演示补全月份。

首先，我们先来建两张表，第一张表命名为薪水，第二张表命名为补贴表：

CREATE TEMPORARY table A(
yf VARCHAR(20),
xs VARCHAR(20)
);
COMMENT on table A is 'A表';
COMMENT on column A.yf is '月份';
COMMENT ON column A.xs is '薪水'; 

insert into A(yf,xs) VALUES('1','4000');
insert into A(yf,xs) VALUES('2','4500');
insert into A(yf,xs) VALUES('5','4200');
insert into A(yf,xs) VALUES('6','5500');
insert into A(yf,xs) VALUES('7','9000');

CREATE TEMPORARY table B(
yf VARCHAR(20),
bt VARCHAR(20)
);
COMMENT on table B is 'B表';
COMMENT on column B.yf is '月份';
COMMENT ON column B.bt is '补贴'; 

insert into B(yf,bt) VALUES('1','1000');
insert into B(yf,bt) VALUES('2','1000');
insert into B(yf,bt) VALUES('5','1000');
insert into B(yf,bt) VALUES('6','1000');

 

 

一、left join

顾名思义，就是“左连接”，以左表为主，查出来的结果显示左边的所有数据，然后右边显示的是和左边有交集部分的数据。如下：

select   * from   A left join B on A.yf = B.yf;

结果集：

 

 

为了效果我把所有的字段都取出来了，实际我们在用的过程中呢，是只取需要的字段

比如：

select   A.*,B.bt from   A left join B on A.yf = B.yf;

 

 

二、right join

“右连接”，以右为主，关联查询左表的数据，查询结果是右边的所有数据以及右表和左表有交集的数据，有点绕哈。如下：

select    * from    A right join B on A.yf = B.yf;

结果集：

 

 

 

三、join

join，是“inner join”的简写，称为“内连接”，以两个表的交集为主，查询结果是两个表有交集的部分，其余没有关联就不额外显示出来，这个用的情况也是挺多的，如下

select    * from    A inner join B on A.yf = B.yf;

结果集：

 

 

以上就是三种连接的介绍，如果觉得效果不太明显，可以自己创建表后替换数据。

 

重点来了，补全月份这个需求是上面的延申，当然我们可以利用循环来做，个人觉得用关联更简单。话不多说，上需求：根据现有数据算出问题月度趋势，实际数据不支持的，因为里面有些月份没有，产品经理说，没有的月份的补全，对应的数值补零。（由于保密协议的关系，数据不便展示，这里我们用上面的表代替）。

   首先我们生成一个序列，包含1月到12月。薪水和补贴给空。

SELECT generate_series(1,12) yf,'' as xs,'' as bt;

 

 

看到这里，一些同学应该就知道该怎么做了。我们以刚才生成的这张表为左表，关联A表和B表。

select t.yf,A.xs,B.bt from(SELECT generate_series(1,12) yf,'' as xs,'' as bt)t 
LEFT JOIN A on t.yf=A.yf LEFT JOIN B on t.yf=B.yf; 

 

 

结果出来，但是有很多null，有些记性好的同学应该还记得，需求中指出，没有的月份的补全，对应的数值补零。所以，紧接着我们需要做一点小优化，利用case when 将null替换掉。

select t.yf, 
case when A.xs is NULL 
then '0' 
else A.xs end as xs,
case when B.bt is NULL then '0' 
else B.bt end as bt  
from(SELECT generate_series(1,12) yf,'' as xs,'' as bt)t 
LEFT JOIN A on t.yf=A.yf LEFT JOIN B on t.yf=B.yf;

 

 

完美！

补充：以left join 为例，理论上讲，以左表为基准进行left join，数据量会和左表的数据量一致。但实际开发过程中，会出现关联过后数据量增多的问题，导致这种现象的原因有很多，一、（小幅度增多）：关联的右表中有重复数据，右表中只要满足关联条件都会被匹配到，从而导致数据量增多。二、（爆炸式的增多）：产生笛卡尔积。产生笛卡尔积的原因一般为关联字段存在空值或数据重复的情况。如何避免笛卡尔积呢？五个字：去重去空值。

 

果然，做笔记是弄懂一个知识点的好方法，有不懂的小伙伴欢迎评论留言哦！