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无重复数据环境

在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语句中JOIN的用法场景分析

本帖子中包含更多资源

火星云矿 | 预约S19Pro，享500抵1000！