SQL中的内连接(inner join)、外连接(left|right join、full join)以及on关键字中涉及分区筛选、null解释

一、简介

本篇幅主要介绍了:

  1. SQL中内连接(inner join)、外连接(left join、right join、full join)的机制;
  2. 连接关键字on上涉及表分区筛选的物理执行及引擎优化;
  3. null在表关联时的情况与执行;

内容适用于常见大数据计算引擎,诸如hive、tez、sparksql、presto(trino)等。考虑到后续引擎版本迭代,具体执行以物理执行计划为准,查看执行计划只需再SQL最前方加上explain关键字即可,比如:explain select t1.id,t2.id as id2 from tablename1 t1 join tablename2 t2 on t1.id=t2.id

阅读者适用于SQL入门还没弃坑的同学。

二、有关sql中null的解读

1. 空这个概念

sql中,人们称之为,一般有三种解释:

  1. 空字符串,''
  2. 不合理,不存在,比如小孩的年龄,如果xx用户都没有小孩,那就不存在这个概念了
  3. 不确定,未知。sql中的null一般做该中解释,未知意味着null与null以及任何只与null的判断都是返回null;sql中的null只能用is null才能返回true

比如:select 5>null;
在这里插入图片描述
select null=null也是返回null
在这里插入图片描述
select null is null返回true
在这里插入图片描述

2. 当null出现在逻辑and和or条件里

and 两边都是true才返回true;
select 1>null and true返回null
在这里插入图片描述

or 两边只要有一边是true就返回true;
select 1>null or true 返回true
在这里插入图片描述

3. and和or的优先级

where field_name1 is not null and field_name2>100 or field_name3 is not null and field_name4>0 or field_name5 is not null

这段sql判断逻辑等于:where (field_name1 is not null and field_name2>100) or (field_name3 is not null and field_name4>0) or field_name5 is not null

先and前后判断,最后剩下几个or的或关系

为提升可读性,当涉及多个and,or条件判断时,尽量用括号括起来(如上);

如果条件放在join的on里边作用一样;

select t1.id,t2.id,t2.field_name2 
from tablename1 t1 
join tablename2 t2 
on 1=null and t1.id=t2.id and t1.dt='20241109' and t2.dt='20241109' and t1.id>100 or t2.id<300

以上sql返回t2.id<300的笛卡尔交集

三、连接机制介绍

首先理解内连接和外连接的机制,然后再看on关键字的作用;on关键字的条件只是判断在什么情况下两个表的记录行会产生关联行为;

有表:tablename1tablename2,两个表都是分区表,且两个表的分区字段名都是dt

1. 内连接:inner join

取交集

样例sql:

select t1.id,t2.id,t2.field_name2 
from tablename1 t1 
join tablename2 t2 
on t1.id=t2.id and t1.dt='20241109' and t2.dt='20241109' and t1.id>100

等于(这个t1.id>100是在join之前得tablescan中filter筛选的):
sql1:

select t1.id,t2.id,t2.field_name2 
from tablename1 t1 
join tablename2 t2 
on t1.id=t2.id 
where  t1.dt='20241109' and t2.dt='20241109' and t1.id>100 and t2.id is not null

等于sql2:

select t1.id,t2.id,t2.field_name2 
from (select id from tablename1 where dt='20241109' and t1.id>100) t1 
join (select id,field_name2 from tablename2 where dt='20241109' and is not null) t2 
on t1.id=t2.id

需要注意的是,有一种sql写法,表里没有join,实际也是按inner join走的,比如下面这段sql:

select t1.appid,t1.target,t1.sq,t2.product_type
from ads_mg_core_target_value t1 ,dim_game_info t2 
where t1.appid=t2.appid

我们打印下执行计划:

explain select t1.appid,t1.target,t1.sq,t2.product_type
from ads_mg_core_target_value t1 ,dim_game_info t2 
where t1.appid=t2.appid

在这里插入图片描述
我这里装的是单机hive,且表体量都很小,所以打印的执行计划最后走的是mapjoin

我们把自动mapjoin给关掉,再看下:set hive.auto.convert.join=false;

在这里插入图片描述
这时候显示的是reduce join了

以上三个sql案例,两个表都只读了20241109一个分区的数据,且t1.id>100都是在读分区map时扫描过滤的(引擎优化)

因为null与任何的判断最终都是返回null(除了isnull判断),所以当sql不涉及isnull判断时,对于on量表关联的字段,引擎会做优化,在tablescan表扫描时将null值给剔掉,故inner join不存在null的倾斜(在map时全剔掉了)

在这里插入图片描述

2. 左右连接:left join | right join

left join 跟right join的机制大致等同,一个保留左表全部数据(left join),一个保留右表全部数据(right join)

我们还是以上面的sql为案例,改成left join
sql-1

select t1.id,t2.id,t2.field_name2 
from tablename1 t1 
left join tablename2 t2 
on t1.id=t2.id and t1.dt='20241109' and t2.dt='20241109' and t1.id>100

这段sql如果t1表全量数据体量很大,那就是灾难了,tablename1全表扫描,因为left join保留左表全部数据。大概率是写sql的人sql写错了;tablename2表只读了一个分区;对于not (t1.dt='20241109' and t1.id>100)部门,on没有符合的条件,t2.field_name2全都是null;

不等于
sql-2

select t1.id,t2.id,t2.field_name2 
from tablename1 t1 
left join tablename2 t2 
on t1.id=t2.id 
where t1.dt='20241109' and t2.dt='20241109' and t1.id>100

不等于sql-3(因为最后还是筛选了t2.dt=20241109,所以这段等于两者取inner join,且t1只扫描了20241101一个分区):

select t1.id,t2.id,t2.field_name2 
from tablename1 t1 
left join tablename2 t2 
on t1.id=t2.id and t1.dt='20241109' and  t1.id>100
where t2.dt='20241109'

等于sql-4:

select t1.id,t2.id,t2.field_name2 
from tablename1 t1 
join (select * from tablename2 where dt='20241109' where id is not null) t2 
on t1.id=t2.id and t1.dt='20241109' and  t1.id>100

3. 全连接:full join

全量接,左右会保留左右两边表的所有记录行,对于on关联不上的,被关联表的字段返回null。
sql-1

select t1.id,t2.id,t2.field_name2 
from tablename1 t1 
full join tablename2 t2 
on t1.id=t2.id and t1.dt='20241109' and t2.dt='20241109' and t1.id>100

这个表最后就是tablename1和tablename2两个表都全表扫描,只有当on条件成立是才会映射被关联表字段,其他关联不上的,被关联表该字段都是null;

如果要筛选分区及条件后再用id full join,只能先用子查询改写再join

select  t1.id,t2.id,t2.field_name2 
from (select t1.id from tablename1 where id>100 and dt='20241109') t1 
full join (select id,field_name2 from tablename2 where dt='20241109') t2 
on t1.id=t2.id 

现在有两个表:
在这里插入图片描述
两个表用id字段full join,也就是t1 full join t2 on t1.id=t2.id

那么结果返回几条呢?

因为null与任何非isnull判断都是返回null,full join保留左右表全部数据,所以该段sql一共返回6行数据,测试sql:

select t1.id as t1_id,t2.id as t2_id 
from 
(select null as idunion all select 1 as id union all select 2 as id 
) t1 
full join 
(select 2 as idunion all select null as id union all select null as id union all select 4 as id 
) t2
on t1.id=t2.id 
order by t1.id desc,t2.id desc 

实际返回如下:

在这里插入图片描述

4. left semi join

这是一种特殊的join,sql中in/exists的实现。相比普通inner join,匹配到一条数据就无须再尝试匹配其他数据了。inner join遇到重复会发散;left semi join只会返回左表的部分(仅on的key参与计算);语法支持的情况,也可以用in去改写。具体看执行计划。

5. 多表关联

当存在多表关联时,一般的执行顺序是从左往右走,比如:

tablename1 t1 
left join tablename2 t2
on t1.id=t2.id 
join tablenamme3 t3
on t1.id2=t3.id2

先t1跟t2 left join,结果再跟t3 join;

有的引擎会基于成本优化,调整join顺序,以上sql可能物理执行时这样的(因为两者结果是等同的,如果t2表很小,t3表很大,可能会做出这种优化,具体看物理执行):

tablename1 t1 
join tablenamme3 t3
on t1.id2=t3.id2
left join tablename2 t2
on t1.id=t2.id 

也会有一种情况,如果多表关联on的关联条件是相同的,比如(都是id):

tablename1 t1 
left join tablename2 t2
on t1.id=t2.id 
join tablenamme3 t3
on t1.id=t3.id

看起来有两个join,实际在大数据中,只跑了一段mr,map时读取三个表的数据,reduce左两个join的关联再返回。

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:http://www.rhkb.cn/news/468631.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

【Linux】软硬链接和动静态库

&#x1f525; 个人主页&#xff1a;大耳朵土土垚 &#x1f525; 所属专栏&#xff1a;Linux系统编程 这里将会不定期更新有关Linux的内容&#xff0c;欢迎大家点赞&#xff0c;收藏&#xff0c;评论&#x1f973;&#x1f973;&#x1f389;&#x1f389;&#x1f389; 文章目…

Orleans集群及Placement设置

服务端界面使用相同的clusterid和serviceid&#xff0c;相同ip地址&#xff0c;不同网关端口号和服务端口号&#xff0c;启动两个silo服务&#xff0c;并使用MySql数据库做Silo间信息同步&#xff0c;实现集群。 silo服务启动代码如下&#xff08;从nuget下载Microsoft.Orleans…

iphone怎么删除重复的照片的新策略

Phone用户常常面临存储空间不足的问题&#xff0c;其中一个主要原因是相册中的重复照片。这些重复项不仅占用了大量的存储空间&#xff0c;还会影响设备的整体性能。本文将向您展示iphone怎么删除重复的照片的方法&#xff0c;包括一些利用工具来自动化这个过程的创新方法。 识…

C++ 的第一个程序

目录 一 . C的第一个程序 二 . 命名空间 2.1 namespace的价值 2.1 namespace 的定义 7.3 命名空间的使用 三 . C输入&输出 四 . 缺省参数 五 . 函数重载 六 . 引用 6.1 引用的概念和定义 6.2 引用的特性 6.3 引用的使用 6.4 const 引用 6.5 指针和引用的关系&…

C#开发基础:WPF和WinForms关于句柄使用的区别

1、前言 在 Windows 应用程序开发中&#xff0c;WPF&#xff08;Windows Presentation Foundation&#xff09;和 WinForms&#xff08;Windows Forms&#xff09;是两种常见的用户界面&#xff08;UI&#xff09;框架。它们各自有不同的架构和处理方式&#xff0c;其中一个显…

WPS Office手机去广高级版

工具介绍功能特点 WPS Office是使用人数最多的移动办公软件&#xff0c;独有手机阅读模式&#xff0c;字体清晰翻页流畅&#xff1b;完美支持文字&#xff0c;表格&#xff0c;演示&#xff0c;PDF等51种文档格式&#xff1b;新版本具有海量精美模版及高级功能 安装环境 [名称…

【Three.js基础学习】21.Realistic rendering

前言 课程回顾 渲染器 1.色调映射 值意在将高动态范围](HDR)值转换为低动态范围(LDR) Three.is中的色调映射实际上会伪造将LDR转换为HDR的过程&#xff0c;即使颜色不是HDR&#xff0c; 结果会产生非常逼真的渲染效果 THREE .NoToneMapping (default) 无色调映射 THREE.Linear…

TeamTalk知识点梳理一(单聊)

文章目录 db_proxy_serverdb_proxy_server reactor响应处理流程连接池redis连接池MySQL连接池 单聊消息消息如何封装&#xff1f;如何保证对端完整解析一帧消息&#xff1f;协议格式&#xff1f;单聊消息流转流程消息序号&#xff08;msg_id &#xff09;为什么使用redis生成&a…

LLaMA-Factory学习笔记(1)——采用LORA对大模型进行SFT并采用vLLM部署的全流程

该博客是我根据自己学习过程中的思考与总结来写作的&#xff0c;由于初次学习&#xff0c;可能会有错误或者不足的地方&#xff0c;望批评与指正。 1. 安装 1.1 LLaMA-Factory安装 安装可以参考官方 readme &#xff08;https://github.com/hiyouga/LLaMA-Factory/blob/main/…

Linux -- 进程初印象

目录 预备知识 切入点 PCB 看见进程 pid getpid 函数 预备知识 Linux -- 冯诺依曼体系结构&#xff08;硬件&#xff09;-CSDN博客https://blog.csdn.net/2301_76973016/article/details/143598784?spm1001.2014.3001.5501 Linux -- 操作系统&#xff08;软件&#xf…

342--358作业整理(错误 + 重点)

目录 1. 在需要运行的类中 定义 main 方法 2. this 。访问逻辑&#xff1a;先访问本类中&#xff0c;再访问父类中可以访问的成员&#xff08;不包括和本类中重名的成员&#xff09; 3. super 。访问逻辑&#xff1a;super&#xff08;父类对象&#xff09;直接访问父类及以…

Jekins篇(搭建/安装/配置)

目录 一、环境准备 1. Jenkins安装和持续集成环境配置 2. 服务器列表 3. 安装环境 Jekins 环境 4. JDK 环境 5. Maven环境 6. Git环境 方法一&#xff1a;yum安装 二、JenKins 安装 1. JenKins 访问 2. jenkins 初始化配置 三、Jenkins 配置 1. 镜像配置 四、Mave…

【Linux】冯诺依曼体系结构

目录 一、冯诺依曼体系结构二、冯诺依曼体系结构的基本组成三、关于冯诺依曼体系结构的一些问题结尾 一、冯诺依曼体系结构 冯诺依曼体系结构&#xff0c;也称为普林斯顿结构&#xff0c;是现代计算机设计的基础框架。这一体系结构由数学家冯诺依曼在20世纪40年代提出&#xf…

M1M2 MAC安装windows11 虚拟机的全过程

M1/M2 MAC安装windows11 虚拟机的全过程 这两天折腾了一下windows11 arm架构的虚拟机&#xff0c;将途中遇到的坑总结一下。 1、虚拟机软件&#xff1a;vmware fusion 13.6 或者 parallel 19 &#xff1f; 结论是&#xff1a;用parellel 19。 这两个软件都安装过&#xff0…

NAT、代理服务与内网穿透技术全解析

&#x1f351;个人主页&#xff1a;Jupiter. &#x1f680; 所属专栏&#xff1a;Linux从入门到进阶 欢迎大家点赞收藏评论&#x1f60a; 目录 NAT 技术背景NAT IP 转换过程NAPTNAT 技术的缺陷 代理服务器正向代理工作原理功能特点应用场景 反向代理基本原理应用场景 NAT 和代理…

优选算法 - 1 ( 双指针 移动窗口 8000 字详解 )

一&#xff1a;双指针 1.1 移动零 题目链接&#xff1a;283.移动零 class Solution {public void moveZeroes(int[] nums) {for(int cur 0, dest -1 ; cur < nums.length ; cur){if(nums[cur] 0){}else{dest; // dest 先向后移动⼀位int tmp nums[cur];nums[cur] num…

qt配合映美精取图开发

最近开发一个项目&#xff0c;用映美精相机配合halcon做取图开发&#xff0c;由于网上资料小特意写个记录。到映美精官网下载驱动&#xff0c;映美精官网&#xff0c;下载映美精的工具开发包SDK 映美精的SDK下载SDK后找到classlib文件夹 里面就是SDK新建一个qt程序&#xff0c…

华为云计算HCIE-Cloud Computing V3.0试验考试北京考场经验分享

北京试验考场 北京考场位置 1.试验考场地址 北京市海淀区北清路156号中关村环保科技示范园区M地块Q21楼 考试场选择北京&#xff0c;就是上面这个地址&#xff0c;在预约考试的时候会显示地址&#xff0c;另外在临近考试的时候也会给你发邮件&#xff0c;邮件内会提示你考试…

LeetCode 509.斐波那契数

动态规划思想 五步骤&#xff1a; 1.确定dp[i]含义 2.递推公式 3.初始化 4.遍历顺序 5.打印dp数组 利用状态压缩&#xff0c;简化空间复杂度。在原代码中&#xff0c;dp 数组保存了所有状态&#xff0c;但实际上斐波那契数列的计算只需要前两个状态。因此&#xff0c;我们…

反向代理开发

1 概念 1.1 反向代理概念 反向代理是指以代理服务器来接收客户端的请求&#xff0c;然后将请求转发给内部网络上的服务器&#xff0c;将从服务器上得到的结果返回给客户端&#xff0c;此时代理服务器对外表现为一个反向代理服务器。 对于客户端来说&#xff0c;反向代理就相当于…