SQL语句中JOIN的用法场景分析
作者：zhangbeizhen18 发布时间：2024-01-15 05:05:49
记录：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
);
以上，感谢。
来源：https://blog.csdn.net/zhangbeizhen18/article/details/118944620
0
投稿猜你喜欢
1.算法概述决策树算法是在已知各种情况发生概率的基础上，通过构成决策树来求取净现值的期望值大于等于零的概率，评价项目风险，判断其可行性的决策- 参考文档 https://cli.vuejs.org/zh/1.安装npm install -g @vue/cli2.检查安装vue -V
- 安装pip install requests发送网络请求import requestsr=requests.get('http://
- 1. 创建Django项目创建应用前必须先进入虚拟环境 workon npy007创建个文件夹 madir testcd testdjang
- 安装 NumPy如果你已经在系统上安装了 Python 和 PIP，那么安装 NumPy 非常容易。请使用这条命令安装它：C:\Users\
- 开发过程中的数据库结构结构，不可避免的会需要反复的修改。最麻烦的情况莫过于开发者数据库结构已经修改，而实际应用中数据库又有大量数据，如何在不
- 本文实例讲述了python使用reportlab实现图片转换成pdf的方法。分享给大家供大家参考。具体实现方法如下：#!/usr/bin/e
- 使用[[v]*n]*n遇到的坑今天通过[[v]*n]*n快速创建列表, 当我使用索引修改值时, 本来是打算修改a[0][0]这一个元素的第一
- 前言：初识Python语言，觉得python满足了你上学时候对编程语言的所有要求。python语言的高效编程技巧让那些曾经苦逼学了四年c或者
- 那天偶尔看到看到一个小问题：两个不等长列表a=[1,2,3],b=[4,5,6,7]，求它们对应元素的乘积的和。我一开始想到的方法就是选择更
- 一、MongoDB对MySQL常用的SQL语句对应的实现 —————————————— MySQL: SELECT * FROM user
- 众所周知，由于 GIL 的存在，Python 单进程中的所有操作都是在一个CPU核上进行的，所以为了提高运行速度，我们一般会采用多进程的方式
- 一、Python中global与nonlocal 声明如下代码a = 10 def foo(): a = 100执行foo() 结果 a
- 如下所示：import jsonresult = response.read()result.decode('utf-8')
- reflect反射首先，我们要区分两个概念——“标识名”和&
- 下面先来看一个例子：import requestsresult=requests.get("http://data.1
- 最近看JavaScript高级程序设计，大有收获，接下来几天写一下读书笔记。之前写了一篇Ajax初步理解的随笔，里面有个函数用来创建XmlH
- 今天小池提出一个问题讨论，如何使分页做的更友好。做了一些调研和思考，做了些总结。分页在电商网站3级页、搜索结果页面等信息量大的页面是很重要的
- 在使用python通过open()函数来打开文件的时候，传递绝对路径给open()的时候，发现路径参数的内容与想象中的有所出入：由于wind
- 本文实例讲述了Python找出序列中出现次数最多的元素。分享给大家供大家参考，具体如下：问题：找出一个元素序列中出现次数最多的元素是什么解决
