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

数据分析学习社
数据分析学习社
数据分析学习社
348
文章
0
评论
2021-07-2611:37:34 评论 83 2873字
摘要

本文带领大家探讨下sql语句中join的用法,通过场景分析给大家进行详细介绍。

 

写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变得陌生极了。于是想探究一下以作记录。

二、建表

 

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

 

三、SQL语句中使用JOIN无重复数据情况

在SQL语句中使用JOIN无重复数据情况,即在TEST_TB01和TEST_TB02表中均无重复数据情况。分别使用JOIN、INNER JOIN、LEFT JOIN、LEFT OUTER JOIN、RIGHT JOIN、FULL JOIN验证。

在TEST_TB01插入数据:SQL语句中JOIN的用法场景分析

在TEST_TB02插入数据:

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

查看TEST_TB01数据:

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

查看TEST_TB02数据:

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

1.在SQL中使用JOIN

TEST_TB01和TEST_TB02根据part_id使用JOIN连接,只返回两个表(TEST_TB01和TEST_TB02)中连接字段相等的记录。

SQL语句:

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

执行结果:

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

2.在SQL中使用INNER JOIN

TEST_TB01和TEST_TB02根据part_id使用INNER JOIN连接,只返回两个表(TEST_TB01和TEST_TB02)中连接字段相等的记录。INNER JOIN和JOIN效果等价。

SQL语句:

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

执行结果:

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

3.在SQL中使用LEFT JOIN

TEST_TB01和TEST_TB02根据part_id使用LEFT JOIN连接,左连接,返回左表(TEST_TB01)中所有的记录以及右表(TEST_TB02)中连接字段相等的记录。

SQL语句:

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

执行结果:

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语句:

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

执行结果:

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

5.在SQL中使用RIGHT JOIN

TEST_TB01和TEST_TB02根据part_id使用RIGHT JOIN连接,右连接,返回右表(TEST_TB02)中所有的记录以及左表(TEST_TB01)中连接字段相等的记录

SQL语句:

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

执行结果:

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

6.在SQL中使用FULL JOIN

TEST_TB01和TEST_TB02根据part_id使用FULL JOIN连接,外连接,返回两个表中的行:LEFT JOIN + RIGHT JOIN所有行记录。

SQL语句:

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

执行结果:

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

四、SQL语句中使用JOIN有重复数据情况

在SQL语句中使用JOIN有重复数据情况,即在TEST_TB01和TEST_TB02表中均有重复数据情况。分别使用JOIN、INNER JOIN、LEFT JOIN、LEFT OUTER JOIN、RIGHT JOIN、FULL JOIN验证。

在TEST_TB01插入数据:

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

在TEST_TB02插入数据:

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

查看TEST_TB01数据:

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

查看TEST_TB02数据:

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

1.在SQL中使用JOIN

TEST_TB01和TEST_TB02根据part_id使用JOIN连接,只返回两个表(TEST_TB01和TEST_TB02)中连接字段相等的记录。

SQL语句:

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

执行结果:

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

2.在SQL中使用INNER JOIN

TEST_TB01和TEST_TB02根据part_id使用INNER JOIN连接,只返回两个表(TEST_TB01和TEST_TB02)中连接字段相等的记录。INNER JOIN和JOIN效果等价。

SQL语句:

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

执行结果:

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

3.在SQL中使用LEFT JOIN

TEST_TB01和TEST_TB02根据part_id使用LEFT JOIN连接,左连接,返回左表(TEST_TB01)中所有的记录以及右表(TEST_TB02)中连接字段相等的记录。

SQL语句:

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

执行结果:

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语句:

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

执行结果:

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

5.在SQL中使用RIGHT JOIN

TEST_TB01和TEST_TB02根据part_id使用RIGHT JOIN连接,右连接,返回右表(TEST_TB02)中所有的记录以及左表(TEST_TB01)中连接字段相等的记录

SQL语句:

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

执行结果:

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

6.在SQL中使用FULL JOIN

TEST_TB01和TEST_TB02根据part_id使用FULL JOIN连接,外连接,返回两个表中的行:LEFT JOIN + RIGHT JOIN所有行记录。

SQL语句:

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

执行结果:

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数据库建表语句:

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

在MySQL数据库建表语句:

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

以上,感谢。

到此这篇关于SQL语句中JOIN的用法的文章就介绍到这了。

 

 

 

End.

作者:脚本之家--zhangbeizhen18

本文为转载分享,如果涉及作品、版权和其他问题,请联系我们第一时间删除(微信号:lovedata0520)

更多文章前往首页浏览http://www.itongji.cn/

 

  • 我的微信公众号
  • 微信扫一扫
  • weinxin
  • 我的微信公众号
  • 微信扫一扫
  • weinxin
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: