服务器之家：专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Sql Server - SQL语句中JOIN的用法场景分析

SQL语句中JOIN的用法场景分析

2021-08-21 21:56zhangbeizhen18 Sql Server

本文带领大家探讨下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建表语句：

?
1
2
3
4
5
6
create table test_tb01
(
  sensor_id   bigint,
  part_id     bigint
 )
comment '数据表一';

test_tb02建表语句：

?
1
2
3
4
5
6
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插入数据：

?
1
2
3
4
5
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插入数据：

?
1
2
3
4
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数据：

SQL语句中JOIN的用法场景分析

查看test_tb02数据：

SQL语句中JOIN的用法场景分析

1.在sql中使用join

test_tb01和test_tb02根据part_id使用join连接，只返回两个表（test_tb01和test_tb02）中连接字段相等的记录。

sql语句：

?
1
2
3
4
5
6
7
select
  *
from
  test_tb01 aa
join test_tb02 bb
    on aa.part_id = bb.part_id
order by aa.sensor_id asc;

执行结果：

SQL语句中JOIN的用法场景分析

2.在sql中使用inner join

test_tb01和test_tb02根据part_id使用inner join连接，只返回两个表（test_tb01和test_tb02）中连接字段相等的记录。inner join和join效果等价。

sql语句：

?
1
2
3
4
5
6
7
select
  *
from
  test_tb01 aa
inner join test_tb02 bb
    on aa.part_id = bb.part_id
order by aa.sensor_id asc;

执行结果：

SQL语句中JOIN的用法场景分析

3.在sql中使用left join

test_tb01和test_tb02根据part_id使用left join连接，左连接，返回左表(test_tb01)中所有的记录以及右表(test_tb02)中连接字段相等的记录。

sql语句：

?
1
2
3
4
5
6
7
select
  *
from
  test_tb01 aa
left join test_tb02 bb
    on aa.part_id = bb.part_id
order by aa.sensor_id asc;

执行结果：

SQL语句中JOIN的用法场景分析

4.在sql中使用left outer join

test_tb01和test_tb02根据part_id使用left outer join连接，左外连接，返回左表(test_tb01)中所有的记录以及右表(test_tb02)中连接字段相等的记录。left outer join

和left join等价。

sql语句：

?
1
2
3
4
5
6
7
select
  *
from
  test_tb01 aa
left outer join test_tb02 bb
    on aa.part_id = bb.part_id
order by aa.sensor_id asc;

执行结果：

SQL语句中JOIN的用法场景分析

5.在sql中使用right join

test_tb01和test_tb02根据part_id使用right join连接，右连接，返回右表(test_tb02)中所有的记录以及左表(test_tb01)中连接字段相等的记录

sql语句：

?
1
2
3
4
5
6
7
select
  *
from
  test_tb01 aa
right join test_tb02 bb
    on aa.part_id = bb.part_id
order by aa.sensor_id asc;

执行结果：

SQL语句中JOIN的用法场景分析

6.在sql中使用full join

test_tb01和test_tb02根据part_id使用full join连接，外连接，返回两个表中的行：left join + right join所有行记录。

sql语句：

?
1
2
3
4
5
6
7
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有重复数据情况

在sql语句中使用join有重复数据情况，即在test_tb01和test_tb02表中均有重复数据情况。分别使用join、inner join、left join、left outer join、right join、full join验证。

在test_tb01插入数据：

?
1
2
3
4
5
6
7
8
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插入数据：

?
1
2
3
4
5
6
7
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数据：

SQL语句中JOIN的用法场景分析

查看test_tb02数据：

SQL语句中JOIN的用法场景分析

1.在sql中使用join

test_tb01和test_tb02根据part_id使用join连接，只返回两个表（test_tb01和test_tb02）中连接字段相等的记录。

sql语句：

?
1
2
3
4
5
6
7
select
  *
from
  test_tb01 aa
join test_tb02 bb
    on aa.part_id = bb.part_id
order by aa.sensor_id asc;

执行结果：

SQL语句中JOIN的用法场景分析

2.在sql中使用inner join

test_tb01和test_tb02根据part_id使用inner join连接，只返回两个表（test_tb01和test_tb02）中连接字段相等的记录。inner join和join效果等价。

sql语句：

?
1
2
3
4
5
6
7
select
  *
from
  test_tb01 aa
inner join test_tb02 bb
    on aa.part_id = bb.part_id
order by aa.sensor_id asc;

执行结果：

SQL语句中JOIN的用法场景分析

3.在sql中使用left join

test_tb01和test_tb02根据part_id使用left join连接，左连接，返回左表(test_tb01)中所有的记录以及右表(test_tb02)中连接字段相等的记录。

sql语句：

?
1
2
3
4
5
6
7
select
  *
from
  test_tb01 aa
left join test_tb02 bb
    on aa.part_id = bb.part_id
order by aa.sensor_id asc;

执行结果：

SQL语句中JOIN的用法场景分析

4.在sql中使用left outer join

test_tb01和test_tb02根据part_id使用left outer join连接，左外连接，返回左表(test_tb01)中所有的记录以及右表(test_tb02)中连接字段相等的记录。left outer join

和left join等价。

sql语句：

?
1
2
3
4
5
6
7
select
  *
from
  test_tb01 aa
left outer join test_tb02 bb
    on aa.part_id = bb.part_id
order by aa.sensor_id asc;

执行结果：

SQL语句中JOIN的用法场景分析

5.在sql中使用right join

test_tb01和test_tb02根据part_id使用right join连接，右连接，返回右表(test_tb02)中所有的记录以及左表(test_tb01)中连接字段相等的记录

sql语句：

?
1
2
3
4
5
6
7
select
  *
from
  test_tb01 aa
right join test_tb02 bb
    on aa.part_id = bb.part_id
order by aa.sensor_id asc;

执行结果：

SQL语句中JOIN的用法场景分析

6.在sql中使用full join

test_tb01和test_tb02根据part_id使用full join连接，外连接，返回两个表中的行：left join + right join所有行记录。

sql语句：

?
1
2
3
4
5
6
7
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有重复与无重复数据区别

在sql语句中使用join有重复数据情况，使用join连接，符合连接字段相等的记录的结果集是笛卡尔积，第一个表的行数乘以第二个表的行数。

六、解决方式

1.先去重再使用join连接

根据业务规则先对test_tb01和test_tb02分别去重再使用join连接。

2.先使用join连接再去重

根据业务规则先对test_tb01和test_tb02使用join连接生成结果集，再对结果集去重。

3.建议

在生产环境特别是数据量大场景，推荐使用第一种方式，先逐个表去重再使用join连接。

七、关系型数据库验证表结构

本例是在dataworks环境（即maxcompute大数据平台）下验证，即在关系型数据库验证除表结构差异，其它均相同。

在oracle数据库建表语句：

?
1
2
3
4
5
6
7
8
9
10
11
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数据库建表语句：

?
1
2
3
4
5
6
7
8
9
10
11
create table test_tb01
(
  sensor_id  bigint,
  part_id  bigint
 );
 
 create table test_tb02
(
  part_id  bigint,
  elem_id  bigint
 );

以上，感谢。

到此这篇关于sql语句中join的用法的文章就介绍到这了,更多相关sql join的用法内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://blog.csdn.net/zhangbeizhen18/article/details/118944620

  • SQL
  • join

    • 延伸 · 阅读

    精彩推荐
    最近更新
    编辑推荐
    阅读排行
    热门标签
    2005 　 18052 　 1355 　 3456 　 29506 　 9003 　 5120 　 21002 　 5173 　 15138 　 15404 　 错误5123 　 SQL导入 　 sql导出 　 新增 　 sp_executesql 　 xp_cmdshell 　 Transact_SQL 　 手册 　 SQL Server2005 　 网络备份 　 日期查询 　 数据库开发 　 SQL Server 2000 　 经典语句 　 删除重复记录 　 收缩日志 　 排序规则 　 基本函数 　 加密函数 　
    © 2019-2023 服务器之家(www.zzvips.com) 版权所有关于我们联系我们版权申明网站地图