冷月葬花魂 · 314浏览 · 发布于2021-07-23
本文带领大家探讨下sql语句中join的用法，本文通过场景分析给大家介绍的很详细，感兴趣的朋友跟随小编一起看看吧
记录：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建表语句：
|
1 2 3 4 5 6 |
|
TEST_TB02建表语句：
|
1 2 3 4 5 6 |
|
三、SQL语句中使用JOIN无重复数据情况
在SQL语句中使用JOIN无重复数据情况，即在TEST_TB01和TEST_TB02表中均无重复数据情况。分别使用JOIN、INNER JOIN、LEFT JOIN、LEFT OUTER JOIN、RIGHT JOIN、FULL JOIN验证。
在TEST_TB01插入数据：
|
1 2 3 4 5 |
|
在TEST_TB02插入数据：
|
1 2 3 4 |
|
查看TEST_TB01数据：
查看TEST_TB02数据：
1.在SQL中使用JOIN
TEST_TB01和TEST_TB02根据part_id使用JOIN连接，只返回两个表（TEST_TB01和TEST_TB02）中连接字段相等的记录。
SQL语句：
|
1 2 3 4 5 6 7 |
|
执行结果：
2.在SQL中使用INNER JOIN
TEST_TB01和TEST_TB02根据part_id使用INNER JOIN连接，只返回两个表（TEST_TB01和TEST_TB02）中连接字段相等的记录。INNER JOIN和JOIN效果等价。
SQL语句：
|
1 2 3 4 5 6 7 |
|
执行结果：
3.在SQL中使用LEFT JOIN
TEST_TB01和TEST_TB02根据part_id使用LEFT JOIN连接，左连接，返回左表(TEST_TB01)中所有的记录以及右表(TEST_TB02)中连接字段相等的记录。
SQL语句：
|
1 2 3 4 5 6 7 |
|
执行结果：
4.在SQL中使用LEFT OUTER JOIN
TEST_TB01和TEST_TB02根据part_id使用LEFT OUTER JOIN连接，左外连接，返回左表(TEST_TB01)中所有的记录以及右表(TEST_TB02)中连接字段相等的记录。LEFT OUTER JOIN
和LEFT JOIN等价。
SQL语句：
|
1 2 3 4 5 6 7 |
|
执行结果：
5.在SQL中使用RIGHT JOIN
TEST_TB01和TEST_TB02根据part_id使用RIGHT JOIN连接，右连接，返回右表(TEST_TB02)中所有的记录以及左表(TEST_TB01)中连接字段相等的记录
SQL语句：
|
1 2 3 4 5 6 7 |
|
执行结果：
6.在SQL中使用FULL JOIN
TEST_TB01和TEST_TB02根据part_id使用FULL JOIN连接，外连接，返回两个表中的行：LEFT JOIN + RIGHT JOIN所有行记录。
SQL语句：
|
1 2 3 4 5 6 7 |
|
执行结果：
四、SQL语句中使用JOIN有重复数据情况
在SQL语句中使用JOIN有重复数据情况，即在TEST_TB01和TEST_TB02表中均有重复数据情况。分别使用JOIN、INNER JOIN、LEFT JOIN、LEFT OUTER JOIN、RIGHT JOIN、FULL JOIN验证。
在TEST_TB01插入数据：
|
1 2 3 4 5 6 7 8 |
|
在TEST_TB02插入数据：
|
1 2 3 4 5 6 7 |
|
查看TEST_TB01数据：
查看TEST_TB02数据：
1.在SQL中使用JOIN
TEST_TB01和TEST_TB02根据part_id使用JOIN连接，只返回两个表（TEST_TB01和TEST_TB02）中连接字段相等的记录。
SQL语句：
|
1 2 3 4 5 6 7 |
|
执行结果：
2.在SQL中使用INNER JOIN
TEST_TB01和TEST_TB02根据part_id使用INNER JOIN连接，只返回两个表（TEST_TB01和TEST_TB02）中连接字段相等的记录。INNER JOIN和JOIN效果等价。
SQL语句：
|
1 2 3 4 5 6 7 |
|
执行结果：
3.在SQL中使用LEFT JOIN
TEST_TB01和TEST_TB02根据part_id使用LEFT JOIN连接，左连接，返回左表(TEST_TB01)中所有的记录以及右表(TEST_TB02)中连接字段相等的记录。
SQL语句：
|
1 2 3 4 5 6 7 |
|
执行结果：
4.在SQL中使用LEFT OUTER JOIN
TEST_TB01和TEST_TB02根据part_id使用LEFT OUTER JOIN连接，左外连接，返回左表(TEST_TB01)中所有的记录以及右表(TEST_TB02)中连接字段相等的记录。LEFT OUTER JOIN
和LEFT JOIN等价。
SQL语句：
|
1 2 3 4 5 6 7 |
|
执行结果：
5.在SQL中使用RIGHT JOIN
TEST_TB01和TEST_TB02根据part_id使用RIGHT JOIN连接，右连接，返回右表(TEST_TB02)中所有的记录以及左表(TEST_TB01)中连接字段相等的记录
SQL语句：
|
1 2 3 4 5 6 7 |
|
执行结果：
6.在SQL中使用FULL JOIN
TEST_TB01和TEST_TB02根据part_id使用FULL JOIN连接，外连接，返回两个表中的行：LEFT JOIN + RIGHT JOIN所有行记录。
SQL语句：
|
1 2 3 4 5 6 7 |
|
执行结果：
五、SQL中使用JOIN有重复与无重复数据区别
在SQL语句中使用JOIN有重复数据情况，使用JOIN连接，符合连接字段相等的记录的结果集是笛卡尔积，第一个表的行数乘以第二个表的行数。
六、解决方式
1.先去重再使用JOIN连接
根据业务规则先对TEST_TB01和TEST_TB02分别去重再使用JOIN连接。
2.先使用JOIN连接再去重
根据业务规则先对TEST_TB01和TEST_TB02使用JOIN连接生成结果集，再对结果集去重。
3.建议
在生产环境特别是数据量大场景，推荐使用第一种方式，先逐个表去重再使用JOIN连接。
七、关系型数据库验证表结构
本例是在DataWorks环境（即MaxCompute大数据平台）下验证，即在关系型数据库验证除表结构差异，其它均相同。
在ORACLE数据库建表语句：
|
1 2 3 4 5 6 7 8 9 10 11 |
|
在MySQL数据库建表语句：
|
1 2 3 4 5 6 7 8 9 10 11 |
|
相关推荐
奔跑的男人 · 804浏览 · 2019-06-03 09:33:43
吴振华 · 663浏览 · 2019-05-14 21:57:51
iamitnan · 723浏览 · 2019-06-18 10:04:56
qq2360248666 · 895浏览 · 2019-05-29 09:48:45
追忆似水年华 · 869浏览 · 2019-05-27 09:54:23
qq2360248666 · 751浏览 · 2019-06-04 10:18:47
0评论