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有重复数据情况，即在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有重复数据情况，使用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的用法内容请搜索开心学习网以前的文章或继续浏览下面的相关文章希望大家以后多多支持开心学习网！
- mysql图形化管理工具
- mysqltruncate分区自定义参数（MySQL truncate table语句的使用）
- Windows2012配置SQLServer2014AlwaysOn的图解（Windows2012配置SQLServer2014AlwaysOn的图解）
- 如何在sql server中解密视图（关于SQL Server加密与解密的问题）
- mysql操作时间的函数（一篇文章搞定Mysql日期时间函数）
- mysql时间戳和datetime对比（mysql中 datatime与timestamp的区别说明）
- sqlserver 使用SSMS运行sql脚本的六种方法（sqlserver 使用SSMS运行sql脚本的六种方法）
- mysql批量生成uuid（一种简单的ID生成策略: Mysql表生成全局唯一ID的实现）
- sql优化100个技巧（52条SQL语句教你性能优化）
- mysqltimestamp如何比较（为什么MySQL 使用timestamp可以无视时区问题.）
- docker查找redis配置文件（解决docker重启redis,mysql数据丢失的问题）
- SQL中Truncate的用法
- mysql怎么使用null（MySQL null与not null和null与空值''''的区别详解）
- mysql innodb存储原理（mysql innodb的重要组件汇总）
- sqlserver修改排序规则几种方法（SQL Server 分页编号的另一种方式推荐）
- mac版本php环境搭建（在Mac OS X中配置Apache＋PHP＋MySQL运行环境的详细步骤）
- 一部手机两套系统 OPPO Find X3的正确打开方式你知道吗（一部手机两套系统）
- OPPO用户看过来 汇总几个春节实用技巧，轻松搞定多设备联动玩法（汇总几个春节实用技巧）
- 北京旅游攻略（北京旅游攻略5日游及其花费）
- 四川旅游攻略（四川旅游攻略自由行攻略）
- 上海迪士尼攻略（上海迪士尼攻略旅游）
- 哪里可以看熊猫（成都哪里可以看熊猫）
热门推荐
- docker从一个容器中退出来（Docker容器迁移到其他服务器的5种方法详解）
- tomcat启动慢什么原因（Tomcat服务器响应过慢解决方案）
- mysql字符串默认长度（MySQL 字符类型大小写敏感）
- 云主机免费体验（免费云主机试用一年靠谱吗）
- pandas画布设置数据标签（pandas dataframe添加表格框线输出的方法）
- sftp和vsftp有什么区别（浅谈 FTP、FTPS 与 SFTP的区别）
- echarts 高级图表教程（Echarts图例组件的属性与源代码）
- php怎么设计状态模式（php设计模式之观察者模式定义与用法经典示例）
- 数据推入阿里云rds（将阿里云RDS中的数据库迁移到本机自建数据库的可用过程RDS数据迁移）
- python中startswith使用教程（Python os.access用法实例）
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9