sql语句left+join详解(SQL语句中JOIN的用法场景分析)
sql语句left+join详解
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建表语句:
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无重复数据情况,即在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 );
以上,感谢。
到此这篇关于SQL语句中JOIN的用法的文章就介绍到这了,更多相关SQL JOIN的用法内容请搜索开心学习网以前的文章或继续浏览下面的相关文章希望大家以后多多支持开心学习网!
- sql查询union怎么用(SQL语句之Union和Union All的用法)
- sql两列数据快速对比(在sql中对两列数据进行运算作为新的列操作)
- mysql简单查询基本语句(详解MySQL 查询语句的执行过程)
- mysql拼接和过滤(mysql 如何动态修改复制过滤器)
- mysql并发查询优化(详解MySQL 联合查询优化机制)
- mysql如何解决主从延迟(MySQL主从延迟问题解决)
- mysql数据库触发器(MySQL中使用游标触发器的方法)
- SQL Union和Union All的用法和区别
- sql server事务回滚(SQL Server 添加Delete操作回滚日志方式)
- mysql快速创建索引(MySQL创建高性能索引的全步骤)
- mysql数据库数据分析(详解MySQL数据库千万级数据查询和存储)
- mysql里修改密码命令(MySQL修改账号密码方法大全小结)
- LINQ TO SQL 中join
- mysql触发器语法规则(MySQL 触发器的使用及需要注意的地方)
- mysql中的null是什么(浅谈为什么Mysql数据库尽量避免NULL)
- mysql索引提高效率(MySql如何查看索引并实现优化)
- 小说 顾瑾岚拿出一套飞行棋,别说你连飞行棋都不会哦(顾瑾岚拿出一套飞行棋)
- 金品公司 界界乐中秋限定飞行棋礼盒 露营藤篮礼盒全新上市(界界乐中秋限定飞行棋礼盒)
- 必看 8月,相比七夕,更需要注意的是这些事(必看8月相比七夕)
- 8月23日11时16分将迎处暑,逐渐进入气象意义上的秋天(8月23日11时16分将迎处暑)
- 花不语 下 如果重来一次的话,你还会这么选择吗(花不语下如果重来一次的话)
- 城市记忆之上海 最难忘的是老弄堂里的市井味道(城市记忆之上海)
热门推荐
- linux中swap分区的作用是(linux swap交换分区详解)
- mysql密码找回方法(MySQL8忘记密码的快速解决方法)
- reacthooks基础使用(React 小技巧教你如何摆脱hooks依赖烦恼)
- docker怎么连主机数据库(docker 安装nacos并配置数据库的教程详解)
- fileoutstream使用方法(Filestream使用简单步骤总结)
- 使用谷歌浏览器模拟移动端打开网页
- asp.net func 委托
- mysql带log的版本(聊聊MYSQL中Redo Log是什么?)
- phpzip压缩原理(PHP生成zip压缩包的常用方法示例)
- python pdf文件操作(Python常见读写文件操作实例总结文本、json、csv、pdf等)