记录：256
写sql最高境界：select * from 表名。当然这是一句自嘲。探究一下sql语句中join的用法，直到经历这个场景，变得想验证一下究竟。
一、场景
把关系型数据库a中表test_tb01和test_tb02迁移到大数据平台m（maxcompute大数据平台）。test_tb01单表1000万条记录，test_tb02单表80万条记录。
在关系型数据库中，test_tb01和test_tb02中有主键约束。在产生新增业务数据时，不会存在重复数据插入。但是，当数据迁移到大数据平台后，由于在大数据平台中无主键约束功能。在产生新增业务数据时，test_tb01和test_tb02均均插入了重复数据。
在一个计算任务中，test_tb01和test_tb02根据某个字段join连接，计算出了一份结果数据，数据推送到使用方的关系型数据库c。直接导致了c数据库的对应表的表空间撑爆，监控预警。
原因：test_tb01和test_tb02有重复数据，使用join连接后，生成了10亿+条数据，共计200g+数据，直接推送到c数据库。
那次考虑不周，瞬间懵了，感觉sql语句中的join变得陌生极了。于是想探究一下以作记录。
二、建表
test_tb01建表语句：
create table test_tb01 ( sensor_id bigint, part_id bigint ) comment '数据表一';
test_tb02建表语句：
create table test_tb02 ( part_id bigint, elem_id bigint ) comment '数据表二';
三、sql语句中使用join无重复数据情况
在sql语句中使用join无重复数据情况，即在test_tb01和test_tb02表中均无重复数据情况。分别使用join、inner join、left join、left outer join、right join、full join验证。
在test_tb01插入数据：
insert into test_tb01 (sensor_id,part_id) values(2101,9911); insert into test_tb01 (sensor_id,part_id) values(2102,9912); insert into test_tb01 (sensor_id,part_id) values(2103,9913); insert into test_tb01 (sensor_id,part_id) values(2104,9914); insert into test_tb01 (sensor_id,part_id) values(2105,9915);
在test_tb02插入数据：
insert into test_tb02 (part_id,elem_id) values(9911,8901); insert into test_tb02 (part_id,elem_id) values(9912,8902); insert into test_tb02 (part_id,elem_id) values(9913,8903); insert into test_tb02 (part_id,elem_id) values(9916,8906);
查看test_tb01数据：
查看test_tb02数据：
1.在sql中使用join
test_tb01和test_tb02根据part_id使用join连接，只返回两个表（test_tb01和test_tb02）中连接字段相等的记录。
sql语句：
select
*
from
test_tb01 aa
join test_tb02 bb
on aa.part_id = bb.part_id
order by aa.sensor_id asc;
执行结果：
2.在sql中使用inner join
test_tb01和test_tb02根据part_id使用inner join连接，只返回两个表（test_tb01和test_tb02）中连接字段相等的记录。inner join和join效果等价。
sql语句：
select
*
from
test_tb01 aa
inner join test_tb02 bb
on aa.part_id = bb.part_id
order by aa.sensor_id asc;
执行结果：
3.在sql中使用left join
test_tb01和test_tb02根据part_id使用left join连接，左连接，返回左表(test_tb01)中所有的记录以及右表(test_tb02)中连接字段相等的记录。
sql语句：
select
*
from
test_tb01 aa
left join test_tb02 bb
on aa.part_id = bb.part_id
order by aa.sensor_id asc;
执行结果：
4.在sql中使用left outer join
test_tb01和test_tb02根据part_id使用left outer join连接，左外连接，返回左表(test_tb01)中所有的记录以及右表(test_tb02)中连接字段相等的记录。left outer join
和left join等价。
sql语句：
select
*
from
test_tb01 aa
left outer join test_tb02 bb
on aa.part_id = bb.part_id
order by aa.sensor_id asc;
执行结果：
5.在sql中使用right join
test_tb01和test_tb02根据part_id使用right join连接，右连接，返回右表(test_tb02)中所有的记录以及左表(test_tb01)中连接字段相等的记录
sql语句：
select
*
from
test_tb01 aa
right join test_tb02 bb
on aa.part_id = bb.part_id
order by aa.sensor_id asc;
执行结果：
6.在sql中使用full join
test_tb01和test_tb02根据part_id使用full join连接，外连接，返回两个表中的行：left join + right join所有行记录。
sql语句：
select
*
from
test_tb01 aa
full join test_tb02 bb
on aa.part_id = bb.part_id
order by aa.sensor_id asc;
执行结果：
四、sql语句中使用join有重复数据情况
在sql语句中使用join有重复数据情况，即在test_tb01和test_tb02表中均有重复数据情况。分别使用join、inner join、left join、left outer join、right join、full join验证。
在test_tb01插入数据：
insert into test_tb01 (sensor_id,part_id) values(2101,9911); insert into test_tb01 (sensor_id,part_id) values(2102,9912); insert into test_tb01 (sensor_id,part_id) values(2103,9913); insert into test_tb01 (sensor_id,part_id) values(2104,9914); insert into test_tb01 (sensor_id,part_id) values(2105,9915); --造重复数据 insert into test_tb01 (sensor_id,part_id) values(2102,9912); insert into test_tb01 (sensor_id,part_id) values(2103,9913);
在test_tb02插入数据：
insert into test_tb02 (part_id,elem_id) values(9911,8901); insert into test_tb02 (part_id,elem_id) values(9912,8902); insert into test_tb02 (part_id,elem_id) values(9913,8903); insert into test_tb02 (part_id,elem_id) values(9916,8906); --造重复数据 insert into test_tb02 (part_id,elem_id) values(9912,8902); insert into test_tb02 (part_id,elem_id) values(9913,8903);
查看test_tb01数据：
查看test_tb02数据：
1.在sql中使用join
test_tb01和test_tb02根据part_id使用join连接，只返回两个表（test_tb01和test_tb02）中连接字段相等的记录。
sql语句：
select
*
from
test_tb01 aa
join test_tb02 bb
on aa.part_id = bb.part_id
order by aa.sensor_id asc;
执行结果：
2.在sql中使用inner join
test_tb01和test_tb02根据part_id使用inner join连接，只返回两个表（test_tb01和test_tb02）中连接字段相等的记录。inner join和join效果等价。
sql语句：
select
*
from
test_tb01 aa
inner join test_tb02 bb
on aa.part_id = bb.part_id
order by aa.sensor_id asc;
执行结果：
3.在sql中使用left join
test_tb01和test_tb02根据part_id使用left join连接，左连接，返回左表(test_tb01)中所有的记录以及右表(test_tb02)中连接字段相等的记录。
sql语句：
select
*
from
test_tb01 aa
left join test_tb02 bb
on aa.part_id = bb.part_id
order by aa.sensor_id asc;
执行结果：
4.在sql中使用left outer join
test_tb01和test_tb02根据part_id使用left outer join连接，左外连接，返回左表(test_tb01)中所有的记录以及右表(test_tb02)中连接字段相等的记录。left outer join
和left join等价。
sql语句：
select
*
from
test_tb01 aa
left outer join test_tb02 bb
on aa.part_id = bb.part_id
order by aa.sensor_id asc;
执行结果：
5.在sql中使用right join
test_tb01和test_tb02根据part_id使用right join连接，右连接，返回右表(test_tb02)中所有的记录以及左表(test_tb01)中连接字段相等的记录
sql语句：
select
*
from
test_tb01 aa
right join test_tb02 bb
on aa.part_id = bb.part_id
order by aa.sensor_id asc;
执行结果：
6.在sql中使用full join
test_tb01和test_tb02根据part_id使用full join连接，外连接，返回两个表中的行：left join + right join所有行记录。
sql语句：
select
*
from
test_tb01 aa
full join test_tb02 bb
on aa.part_id = bb.part_id
order by aa.sensor_id asc;
执行结果：
五、sql中使用join有重复与无重复数据区别
在sql语句中使用join有重复数据情况，使用join连接，符合连接字段相等的记录的结果集是笛卡尔积，第一个表的行数乘以第二个表的行数。
六、解决方式
1.先去重再使用join连接
根据业务规则先对test_tb01和test_tb02分别去重再使用join连接。
2.先使用join连接再去重
根据业务规则先对test_tb01和test_tb02使用join连接生成结果集，再对结果集去重。
3.建议
在生产环境特别是数据量大场景，推荐使用第一种方式，先逐个表去重再使用join连接。
七、关系型数据库验证表结构
本例是在dataworks环境（即maxcompute大数据平台）下验证，即在关系型数据库验证除表结构差异，其它均相同。
在oracle数据库建表语句：
create table test_tb01 ( sensor_id number(16), part_id number(16) ); create table test_tb02 ( part_id number(16), elem_id number(16) );
在mysql数据库建表语句：
create table test_tb01 ( sensor_id bigint, part_id bigint ); create table test_tb02 ( part_id bigint, elem_id bigint );
以上，感谢。
到此这篇关于sql语句中join的用法的文章就介绍到这了,更多相关sql join的用法内容请搜索www.887551.com以前的文章或继续浏览下面的相关文章希望大家以后多多支持www.887551.com！