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的用法内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们!

时间: 2021-07-20

MySQL 8.0.18 稳定版发布! Hash Join如期而至

MySQL 8.0.18 稳定版(GA)已于昨日正式发布,Hash Join 也如期而至. 快速浏览一下这个版本的亮点! 1.Hash Join Hash Join 不需要任何索引来执行,并且在大多数情况下比当前的块嵌套循环算法更有效. 2.EXPLAIN ANALYZE EXPLAIN ANALYZE 将运行查询,然后生成 EXPLAIN 输出,以及有关优化程序估计如何与实际执行相匹配的其他信息. 3.创建用户时可以随机生成密码 为 CREATE USER, ALTER USER和 SET P

MySQL中(JOIN/ORDER BY)语句的查询过程及优化方法

在MySQL查询语句过程和EXPLAIN语句基本概念及其优化中介绍了EXPLAIN语句,并举了一个慢查询例子: 可以看到上述的查询需要检查1万多记录,并且使用了临时表和filesort排序,这样的查询在用户数快速增长后将成为噩梦. 在优化这个语句之前,我们先了解下SQL查询的基本执行过程: 1.应用通过MySQL API把查询命令发送给MySQL服务器,然后被解析 2.检查权限.MySQL optimizer进行优化,经过解析和优化后的查询命令被编译为CPU可运行的二进制形式的查询计划(quer

sql优化实战 把full join改为left join +union all(从5分钟降为10秒)

今天收到一个需求,要改写一个报表的逻辑,当改完之后,再次运行,发现运行超时. 因为特殊原因,无法访问客户的服务器,没办法查看sql的执行计划.没办法知道表中的索引情况,所以,尝试从语句的改写上来优化. 一.原始语句如下: select isnull(vv.customer_id,v.customer_id) as customer_id, isnull(vv.business_date,replace(v.business_date,'-','')) as business_date, v.pr

SQL联合查询inner join、outer join和cross join的区别详解

对于开发使用到数据库的应用,免不了就要使用联合查询,SQL中常用的联合查询有inner join.outer join和cross join:这三者的区别很多人都应该不是很清楚,包括我自己,下面我们一起来看看,如果你使用join连表,缺陷的情况下是inner join,另外,开发中使用的left join和right join属于outer join,而outer join还包括full join.下面我通过图案让大家认识它们的区别. 现有两张表,Table A 是左边的表.Table B 是右

mysql使用from与join两表查询的区别总结

前言 在mysql中,多表连接查询是很常见的需求,在使用多表查询时,可以from多个表,也可以使用join连接连个表 这两种查询有什么区别?哪种查询的效率更高呢? 带着这些疑问,决定动手试试 1.先在本地的mysql上先建两个表one和two one表 CREATE TABLE `one` ( `id` int(0) NOT NULL AUTO_INCREMENT, `one` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE = InnoDB

mysql中各种常见join连表查询实例总结

本文实例讲述了mysql中各种常见join连表查询.分享给大家供大家参考,具体如下: 通常我们需要连接多个表查询数据,以获取想要的结果. 一.连接可以分为三类: (1) 内连接:join,inner join (2) 外连接:left join,left outer join,right join,right outer join,union,union all (3) 交叉连接:cross join 二.准备需要演示的表: CREATE TABLE `a` ( `id` int(11) uns

mysql多个left join连接查询用法分析

本文实例讲述了mysql多个left join连接查询用法.分享给大家供大家参考,具体如下: mysql查询时需要连接多个表时,比如查询订单的商品表,需要查询商品的其他信息,其他信息不在订单的商品表,需要连接其他库的表,但是连接的条件基本都是商品ID就可以了,先给一个错误语句(查询之间的嵌套,效率很低): SELECT A.order_id, A.wid, A.work_name, A.supply_price, A.sell_price, A.total_num, A.sell_profit,

Mysql join连接查询的语法与示例

连接查询: 是将两个查询(或表)的每一行,以"两两横同对接"的方式,所得到的所有行的结果,即一个表中的某行,跟另一个表中的某行.进行"横向对接",得到一个新行. 连接查询包括以下这些不同形式,连接方式: 交叉连接.内连接.外连接(分:左外连接,右外连接) 连接查询语法: select * from 表名 [连接方式] join 表名 [on 连接条件] where ...; 测试数据: mysql> select * from test; +----+----

MySql中流程控制函数/统计函数/分组查询用法解析

路漫漫其修远兮,吾将上下而求索,又到了周末,我继续带各位看官学习回顾Mysql知识. 上次说到了流程控制函数,那就从流程控制函数来继续学习吧! #五.流程控制函数 #1.if函数:if else的效果 IF(条件表达式,成立返回1,不成立返回2) #与Java三元运算相同 SELECT IF(10>5,'大','小'); SELECT last_name,commission_pct,IF(commission_pct IS NULL,'没奖金呵呵','有奖金嘻嘻') AS 备注 FROM em

MySQL切分查询用法分析

本文实例讲述了MySQL切分查询用法.分享给大家供大家参考,具体如下: 对于大查询有时需要'分而治之',将大查询切分为小查询: 每个查询功能完全一样,但只完成原来的一小部分,每次查询只返回一小部分结果集. 删除旧的数据就是一个很好地例子.定期清理旧数据时,如果一条sql涉及了大量的数据时,可能会一次性锁住多个表或行,耗费了大量的系统资源,却阻塞了其他很多小的但重要的查询.将一个大得DELETE语句切分为较小的查询时,可以尽量减少影响msql的性能,同时减少mysql复制造成的延迟. 例如,每个月

SQL多表连接查询实例分析(详细图文)

新建两张表: 表1:student  截图如下: 表2:course  截图如下: (此时这样建表只是为了演示连接SQL语句,当然实际开发中我们不会这样建表,实际开发中这两个表会有自己不同的主键.) 一.外连接外连接可分为:左连接.右连接.完全外连接. 1.左连接  left join 或 left outer join SQL语句:select * from student left join course on student.ID=course.ID执行结果: 左外连接包含left joi

mysql连接查询、联合查询、子查询原理与用法实例详解

本文实例讲述了mysql连接查询.联合查询.子查询原理与用法.分享给大家供大家参考,具体如下: 本文内容: 连接查询 联合查询 子查询 from子查询 where子查询 exists子查询 首发日期:2018-04-11 连接查询: 连接查询就是将多个表联合起来查询,连接查询方式有内连接.外连接.自然连接.交叉连接.连接查询使得可以同时查看多张表中数据. 内连接:有条件连接,多个表之间依据指定条件连接,匹配结果是保留符合匹配结果的记录. 外连接:与内连接不同的是不管匹配符不符合都保留,根据外连接

MySQL查询优化:连接查询排序limit(join、order by、limit语句)介绍

不知道有没有人碰到过这样恶心的问题:两张表连接查询并limit,SQL效率很高,但是加上order by以后,语句的执行时间变的巨长,效率巨低. 情况是这么一个情况:现在有两张表,team表和people表,每个people属于一个team,people中有个字段team_id. 下面给出建表语句: 复制代码 代码如下: create table t_team ( id int primary key, tname varchar(100) ); create table t_people (

MySQL中基本的多表连接查询教程

一.多表连接类型 1. 笛卡尔积(交叉连接) 在MySQL中可以为CROSS JOIN或者省略CROSS即JOIN,或者使用','  如: 由于其返回的结果为被连接的两个数据表的乘积,因此当有WHERE, ON或USING条件的时候一般不建议使用,因为当数据表项目太多的时候,会非常慢.一般使用LEFT [OUTER] JOIN或者RIGHT [OUTER] JOIN 2.   内连接INNER JOIN 在MySQL中把I SELECT * FROM table1 CROSS JOIN tabl

详解MySql基本查询、连接查询、子查询、正则表达查询

查询数据指从数据库中获取所需要的数据.查询数据是数据库操作中最常用,也是最重要的操作.用户可以根据自己对数据的需求,使用不同的查询方式.通过不同的查询方式,可以获得不同的数据.MySQL中是使用SELECT语句来查询数据的.在这一章中将讲解的内容包括. 1.查询语句的基本语法 2.在单表上查询数据 3.使用聚合函数查询数据 4.多表上联合查询 5.子查询 6.合并查询结果 7.为表和字段取别名 8.使用正则表达式查询 什么是查询? 怎么查的? 数据的准备如下: create table STUD

MySQL Union合并查询数据及表别名、字段别名用法分析

本文实例讲述了MySQL Union合并查询数据及表别名.字段别名用法.分享给大家供大家参考,具体如下: union关键字 SELECT s_id, f_name, f_price FROM fruits WHERE f_price < 9.0 UNION ALL SELECT s_id, f_name, f_price FROM fruits WHERE s_id IN(101,103); 为表取别名 SELECT * from orders AS o WHERE o.o_num = 3000