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
![](https://www.aspxhome.com/images/zang.png)
![](https://www.aspxhome.com/images/jiucuo.png)
猜你喜欢
- 正则表达式有两种基本的操作,分别是匹配和替换。匹配就是在一个文本字符串中搜索匹配一特殊表达式;替换就是在一个字符串中查找并替换匹
- 正在看的ORACLE教程是:自动备份Oracle数据库。
- 本文实现利用python的socketserver这个强大的模块实现套接字的并发,具体内容如下目录结构如下:测试文件请放在server_fi
- 界面文件 Ui_ControlBoard.py# -*- coding: utf-8 -*-# Form implementation ge
- 什么是变量在Python编程语言中,变量是用于存储数据值的标识符。它们可以用来引用数据值,而不是直接使用值本身。可以使用等号(=)运算符来将
- asp之家注:为什么要防止访客频繁刷新页面呢?也许你会说他想刷新就让他刷新吧,没什么关系,而且还增加了网页的PV,呵呵。但是有的页面我们可能
- 一.字典的基本方法1.新建字典1)、建立一个空的字典>>> dict1={} >>> dict2=dic
- 代码如下:'返回指定文件夹中文件的数目,传入值为被检测文件夹的硬盘绝对路径 function CountFile
- PyCharm是由JetBrains打造的一款Python IDE,VS2010的重构插件Resharper就是出自JetBrains之手。
- 本文实例讲述了Python2比较当前图片跟图库哪个图片相似的方法。分享给大家供大家参考,具体如下:# -*- coding: utf-8 -
- 首先谈谈它们的共同点吧:它们本质上都是一种对资源的独占锁定,都是由并发引起(如果数据库只有一个session,就谈不上锁定)。接着着重谈谈它
- Pytorch数据类型与转换(torch.tensor,torch.FloatTensor)之前遇到转为tensor转化为浮点型的问题,今天
- 首先你得引入bootstrap与jquery推荐一个CDN:http://cdn.gbtags.com/index.html然后就是开始编写
- IF函数IF(expression ,expr_true, expr_false);MySQL的IF()函数,接受三个表达式,如果第一个表达
- 有的时候取出全部数据库记录也许正好满足你的要求,不过,在大多数情况下我们通常只需得到部分记录。这样一来该如何设计查询呢?当然会更费点脑筋了,
- 目录distinctgroup byrow_number在使用SQL提数的时候,常会遇到表内有重复值的时候,比如我们想得到 uv (独立访客
- XML和XSLT的转换使Web设计受益无穷。借助XML和 XSLT转换,你可以实现将动态用语(dynamic verbiage)和网站内容存
- 进程、线程和协程之间的关系和区别也困扰我一阵子了,最近有一些心得,写一下。进程拥有自己独立的堆和栈,既不共享堆,亦不共享栈,进程由操作系统调
- 本文实例讲述了Python json模块dumps、loads操作。分享给大家供大家参考,具体如下:python中json数据的使用。dum
- DDPDistributed Data Parallel 简称 DDP,是 PyTorch 框架下一种适用于单机多卡、多机多卡任务的数据并行