数据库技术:SQL语句中JOIN的用法场景分析

记录:256写sql最高境界:select * from 表名。当然这是一句自嘲。探究一下sql语句中join的用法,直到经历这个场景,变得想验证一下究竟。一、场景把关系型数据库a中表test_tb0

记录: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建表语句:

%ignore_pre_1%

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数据:

SQL语句中JOIN的用法场景分析

查看test_tb02数据:

SQL语句中JOIN的用法场景分析

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;

执行结果:

SQL语句中JOIN的用法场景分析

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;

执行结果:

SQL语句中JOIN的用法场景分析

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;

执行结果:

SQL语句中JOIN的用法场景分析

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;

执行结果:

SQL语句中JOIN的用法场景分析

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;

执行结果:

SQL语句中JOIN的用法场景分析

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有重复数据情况

在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数据:

SQL语句中JOIN的用法场景分析

查看test_tb02数据:

SQL语句中JOIN的用法场景分析

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;

执行结果:

SQL语句中JOIN的用法场景分析

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;

执行结果:

SQL语句中JOIN的用法场景分析

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;

执行结果:

SQL语句中JOIN的用法场景分析

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;

执行结果:

SQL语句中JOIN的用法场景分析

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;

执行结果:

SQL语句中JOIN的用法场景分析

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有重复与无重复数据区别

在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的用法内容请搜索<猴子技术宅>以前的文章或继续浏览下面的相关文章希望大家以后多多支持<猴子技术宅>!

需要了解更多数据库技术:SQL语句中JOIN的用法场景分析,都可以关注数据库技术分享栏目—猴子技术宅(www.ssfiction.com)

本文来自网络收集,不代表猴子技术宅立场,如涉及侵权请点击右边联系管理员删除。

如若转载,请注明出处:https://www.ssfiction.com/sqljc/676134.html

发表评论

邮箱地址不会被公开。 必填项已用*标注