一篇文章看懂MySQL的多表连接（包含左/右/全外连接）

MySQL的多表查询

这是第二次学习多表查询，关于左右连接还是不是很熟悉，因此重新看一下。小目标：一篇文章看懂多表查询！！

这篇博客是跟着宋红康老师学习的，点击此处查看视频，关于数据库我放在了Github中/Learn/MySQL/尚硅谷MySQL:资料下的aiguigu.sql文件中，可以直接点此处跳转到文件。

由于只有一个文件，因此在这里说一个Github的使用小技巧：在Github的页面中点击。按钮可以打开网页端的VSCode，然后可以直接对整个文档进行Copy，然后在本地新建一个.sql文件粘贴进去就可以了。（Google Chrome浏览器是支持的，Firefox应该也可以，Safari测试了一下不支持，大家可以自行尝试。）

😈 1 v N | 一对多的情况

本办法实现多表查询：查找Abel所在的城市（查询结果就不写了，可以自己再Navicat或者命令行里面试一下）

SELECT  * FROM employees WHERE last_name='Abel'; # 先查找姓Abel的人 得到部门ID是80

SELECT * FROM departments WHERE department_id=80; # 查找部门id是80的部门信息，得到城市ID是2500

SELECT * FROM locations WHERE location_id=2500; # 查看城市ID是2500的城市信息

通过上述方式，我们可以在Service里面调用三次Mapper/SQL，得到结果，但是会大大增加网络IO，且查询结果不确定，甚至还要增加事务管理，完全犯不上，因此可以考虑使用多表查询。

或者将这些内容全部整合成一张表，比如员工表整合员工的城市ID（或者是城市信息）或者在部门ID整合城市信息，甚至可以将三张表整合为一张表，每个员工都包含部门、城市信息，但是考虑到在大型项目中，可能会有几百几千张表，那么将这些表全部整合的话，会增加每次查询的吞吐量（每次查询可能都得几Mb的数据）是非常不便的，将表分开可以让我们达到需要什么数据就查询什么表的状态（大部分情况下可能都是单表查询），因此在开发之前应该考虑到如何分表，以方便数据库的使用以及最合理的使用数据库缓存。

🎨 多表查询如何实现？

🦈 笛卡尔积问题 | 交叉连接问题

直接从表里面查询employee_id与department_id，查询结果有2889条记录

SELECT employee_id,department_name FROM employees,departments; ## 查询结果有2000多条记录，但是员工才只有107个，这明显是不对的。

上述问题涉及到了一个叫笛卡尔积的问题（交叉连接问题），也就是A表中的每一条数据都与B表中的每一条数据组合成为一个查询结果

上图就是一个非常明显的笛卡尔积（但是不能被称为问题），A表中的每一条数据都与B表中的每一条数据做了对应，得到了一个a*b长度的结果，这个结果集就是笛卡尔积。

在实际使用中，我们可能只想要A表中数字与B表中数字相同的结果做组合，得到的结果就是 11/22/33，因此笛卡尔积的情况

加上连接条件之后就变成如下：

SELECT employee_id,department_name FROM employees,departments WHERE employees.`department_id`=departments.`department_id`;

在查询的时候， 我们每次都要写表名，这样可能会比较麻烦，可以通过取别名（使用空格或者AS关键字）的方式进行替换。如下：

SELECT emp.employee_id,dep.department_name FROM employees emp,departments dep WHERE emp.`department_id`=dep.`department_id`;
SELECT emp.employee_id,dep.department_name FROM employees AS emp,departments AS dep WHERE emp.`department_id`=dep.`department_id`;

注意，一旦使用别名，那么我们就需要替换查询语句中的所有的表名，比如将所有的表名employees替换为emp，取别名的方式仅在本次查询生效，不会影响数据库本身。

另外，多表查询的时候应该在查询结果字段前加上表名，这是一种SQL优化方式，可以避免字段太多时MySQL去各个表中查找该字段；

果查询的字段出现在了多个表中，则必须要加上所属表。

如果有n个表实现多表查询，则至少有n-1个链接条件

🎨 多表查询 | 连接方式

等值连接 VS 非等值连接

自连接 VS 非自连接

内连接 VS 外连接

等值连接 VS 非等值连接

在员工表中，存在员工的工资，而在工资表中，存储的是工资的等级，每个等级有上下限，也就是说在某个区间内的工资可以认为是等级X；现在要我们查询员工的id、last_name以及工资等级，因此我们需要使用得等值连接（大于小于或者between）来连接两个表

SELECT e.last_name,e.salary,j.grade_level # 查询结果的关键字
FROM employees AS e,job_grades AS j # 查询的表（取别名）
WHERE e.`salary` BETWEEN j.lowest_sal AND j.highest_sal # 查询条件
ORDER BY e.salary DESC; # 排个序（降序），看着方便

自连接 VS 非自连接

自连接如下：

在员工表中，每一个员工都有一个管理者ID，该ID同时又是此表中其他员工的ID，因此可以通过自连接的方式进行查询：同一个表，去两个别名。

SELECT e.`manager_id` '员工ID',e.last_name AS '员工姓名',m.employee_id '管理者ID',m.last_name AS '管理者姓名'
FROM employees e,employees m
WHERE e.`manager_id` = m.`employee_id`;

以上查询中，我们分别给employees表取了两个别名，分别是e（employees/员工）m(manager/管理者)，将这一张表看做是两张表，然后通过条件将它们关联起来。

注意：查询结果中我特意使用了别名，加不加AS关键字都可以，别名可以更加方便阅读。

内连接 VS 外连接 （有点难但是很重要）

内连接：（上面写的全都是内连接）连接条件只是将几个表的某些字段做了一个简单的对应的就是内连接，并没有用到这些表的所有字段。

外连接：与内连接字段的匹配不同，外连接是行的匹配。（比如用户表有id、name、age，那么id就是一个字段，name、age分别为两个字段，但是id、name、age这三个字段组成一个行，在数据库中作为一条记录显示，将此行与另一个表的行做链接，就被称为外连接）


在讲内外链接之前，我们要了解JOIN ... ON...的写法，JOIN...ON...的写法并不只是适用于外连接，同时也适用于内连接。我们可以通过[INNER] JOIN ... ON ... 的方式来实现一个内连接（INNER可省略）；将需要关联的表放在JOIN后面，条件放在ON里面（不用写WHERE关键字）

与逗号分隔符+WHERE关键字的区别：WHERE关键字的话写法上更加简单，但是（个人觉得）可阅读性较差，而JOIN...ON...的方式写法上比较复杂，但是阅读星（也没有好到哪里去）。

JOIN...ON...还有一点区别于WHERE的是：WHERE只可以写一次，然后里面如果有多个条件可以使用分隔符分开，但是JOIN可以使用类似于链式编程的方式，也就是JOIN ... ON ... JOIN ... ON ... JOIN ... ON... ... 这样的写法，将每一个表的条件单独写在一个ON中。


外连接分为：满外连接、左外连接、右外连接

如果是左外连接，那么左表就是主表，右表称为从表；右外连接与之相反。左外连接出了返回满足连接条件的行之外，还会返回左表中不满足条件的行；右外连接与之相反。

注意！！SQL92语法与SQL99语法实现外连接有所不同，SQL92比较简单一些，但是可读性较差，但是MySQL不支持SQL92语法。的方式。

正如本小节第二段所介绍，内外连接都可以使用JOIN...ON...，区别在于内连接前面（可选）可以写一个INNER关键字，而外连接则必须要写LEFT/RIGHT [OUTER]关键字，如下（下面的例子也有SQL92语法，建议在Oracle数据库中进行尝试）

🍁 左外右外连接

左右外连接可以查询到主表对应从表是空的内容，也就是说主表中的关键字全部都显示出来。

查询全部员工的last_name与department_name信息，其中last_name存储在员工表中，department_name存储在部门表中。

由于老板并没有所属的部门，因此总共员工107，如果不使用外连接的话只会查询到106条记录，因此要使用外连接。

SQL92语法实现外连接，使用+加号 （注意：MySQL不支持SQL92语法）

SELECT e.employee_id,d.department_name
FROM employees e,departments d
WHERE e.`department_id` = d.`department_id`(+);

SQL99语法中实现左外连接

SELECT last_name,department_name
FROM employees e LEFT OUTER JOIN departments d
ON e.`department_id`=d.`department_id`;

以上查询可以查找到107条数据，也就是没有部门的老板也能找到。如果将LEFT换成RIGHT的话，则会查到更多的结果，因为非常多的部门里面是没有用户的。

🍁 满外连接

满外连接的关键字是FULL，但是很遗憾，MySQL还是不支持（Oracle数据库支持，只要将LEFT/RIGHT关键字换为FULL就可以，在此不举例子了）。

如果要在MySQL中使用满外连接，需要使用UBION关键字查询结果合并，如下图所示：

👾 UNION 与 UNION ALL 关键字 | 合并查询结果 | MySQL中满外连接的实现

什么是UNION关键字，UNION关键字可以将两个查询结果合并起来，比如我们要得到一个满外连接， 就可以将下图左上角的图+右中的图相加，得到左下角的图。

UNION ALL关键字：UNION ALL关键字不会去除重复数据，左上与右上图相加，就是一个UNION ALL，因为中间的部分不会删除

在开发中，能用UBION ALL就用UNION ALL，坚决少用UNION（因为会多一个去重，增加时间）

满外连接也属于外连接的一种，由于MySQL不支持FULL关键字，因此变得比较复杂，所以单独做一个小节

上图中7中JOIN的实现

🥥 中间的图 ： 内连接

# 106 条记录
SELECT employee_id,department_name
FROM employees e JOIN departments d
ON e.`department_id` = d.`department_id`;

🥥 左上图： 左外连接

# 107 条记录
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id`= d.`department_id`;

🥥 右上图： 右外连接

# 122 条记录
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id`= d.`department_id`;

🥥 左中图

左中图在左上图的基础上过滤掉了中间的图

# 1 条记录
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id`= d.`department_id`
WHERE d.department_id IS NULL;

🥥 右中图

与左中图同理，都是删除了中间部分

SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id`= d.`department_id`
WHERE d.department_id IS NULL;

🥥 左下图： 全外连接

左上+右中 | UNION ALL
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id`= d.`department_id`
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id`= d.`department_id`
WHERE d.department_id IS NULL;
左中+右上 | UNION ALL
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id`= d.`department_id`
WHERE d.department_id IS NULL
UNION ALL 
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id`= d.`department_id`;
左上+右上 | UNION （不推荐）
SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id`= d.`department_id`
UNION
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id`= d.`department_id`;

🥥 右下图 | 左中+右中

SELECT employee_id,department_name
FROM employees e LEFT JOIN departments d
ON e.`department_id`= d.`department_id`
WHERE d.department_id IS NULL
UNION ALL
SELECT employee_id,department_name
FROM employees e RIGHT JOIN departments d
ON e.`department_id`= d.`department_id`
WHERE d.department_id IS NULL;

自然连接 | SQL 99 语法新特性

NATURAL JOIN，会自动查询两张链接表中所有相同字段，然后进行等值连接

SELECT employee_id,last_name,department_name
FROM employees e NATURAL JOIN departments d;

方便，但是不够灵活

USING | SQL 99 语法新特性

SELECT employee_id,last_name,department_name
FROM employees e JOIN departments d
USING (department_id)

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

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

大神们分享STM32的学习方法

大神们分享STM32的学习方法

单片机用处这么广，尤其是STM32生态这么火！如何快速上手学习呢？ 第一：你要考虑的是，要用STM32实现什么 为什么使用STM32而不是8051? 是因为51的频率太低，无法满足计算需求?是51的管脚太少，无法…
阅读更多...
云HIS（二级医院，乡镇医院，民营医院，标准化HIS医院信息管理系统源码）

云HIS（二级医院，乡镇医院，民营医院，标准化HIS医院信息管理系统源码）

传统 HIS（基于医院信息系统） 和云 HIS（基于云计算的医院信息系统）各有优缺点，选择哪种系统需要根据具体情况进行权衡。 传统 HIS 系统通常由医院自行开发和维护，适用于医院内部信息化程度较高、数据安全性…
阅读更多...
【软件测试】第1章 软件测试概述

【软件测试】第1章 软件测试概述

系列文章目录 文章目录 系列文章目录前言第1章 软件测试概述1.1 软件、软件危机和软件工程1.1.1 基本概念1.1.2 软件工程的目标及其一般开发过程1.1.3 软件过程模型 1.2 软件缺陷与软件故障1.2.1 基本概念1.2.2 典型案例 1.3 软件测试的概念1.3.1 软件测试的定义1.3.2 软件测试…
阅读更多...
计算机程序安装及使用须知_kaic

计算机程序安装及使用须知_kaic

安装及使用须知 1 数据库建模程序的使用 本文件夹中的“PowerDesigner建模”目录下包含三个可运行文件TMS1.cdm，TMS.cdm，TMS.pdm分别为TMS系统的实体关系简图、实体关系图和数据库模型，使用PowerDesigner集成开发环境打开任意一个文件即可运…
阅读更多...
Linux系统与shell编程第一节课

Linux系统与shell编程第一节课

目录 1.1 Linux发展历史 1.2 什么是linux？ 1.3 Linux的发行版 Host-Only（仅主机模式） windows开发 linux服务 区块链， 特点：稳定，安全，可移植性，低资源消耗，开源软…
阅读更多...
2023年第十二届数据技术嘉年华（DTC）资料分享

2023年第十二届数据技术嘉年华（DTC）资料分享

第十二届数据技术嘉年华（DTC 2023）已于4月8日在北京圆满落幕，大会围绕“开源融合数智化——引领数据技术发展，释放数据要素价值”这一主题，共设置有1场主论坛，12场专题论坛，68场主题演讲&#x…
阅读更多...
【基础】Kafka -- 日志存储

【基础】Kafka -- 日志存储

Kafka -- 日志存储 日志文件目录日志索引偏移量索引时间戳索引 日志清理日志删除基于时间基于日志大小基于日志起始偏移量 日志压缩 日志文件目录 Kafka 中的消息以主题为单位进行基本归类，而每个主题又可以划分为一个或者多个分区。在不考虑多副本的情况下&#x…
阅读更多...
【MySQL】插入文件路径，反斜杠消失

【MySQL】插入文件路径，反斜杠消失

系列文章 C#底层库–MySQL脚本自动构建类（insert、update语句生成） 本文链接：https://blog.csdn.net/youcheng_ge/article/details/129179216 C#底层库–MySQL数据库访问操作辅助类（推荐阅读） 本文链接：h…
阅读更多...
如何优雅的写个try catch的方式！

如何优雅的写个try catch的方式！

软件开发过程中，不可避免的是需要处理各种异常，就我自己来说，至少有一半以上的时间都是在处理各种异常情况，所以代码中就会出现大量的try {...} catch {...} finally {...} 代码块，不仅有大量的冗余代码，而…
阅读更多...
07 【Sass语法介绍-控制指令】

07 【Sass语法介绍-控制指令】

1.前言 Sass 为我们提供了很多控制指令，使得我们可以更高效的来控制样式的输出，或者在函数中进行逻辑控制。本节内容我们就来讲解什么是 Sass 控制指令？它能用来做什么？它将使你更方便的编写 Sass 。 2.什么是 Sass 控制指令 控…
阅读更多...
Dockere-Compose迁移Gitea部署

Dockere-Compose迁移Gitea部署

Dockere-Compose迁移Gitea部署 ps: 江湖不是打打杀杀，江湖是人情事故。 解释： Gitea：类似于Git的代码版本管理工具。Docker：Docker-Compose： Docker命令： 查看镜像：docker images 删除镜像…
阅读更多...
2023年江苏专转本成绩查询步骤

2023年江苏专转本成绩查询步骤

2023年江苏专转本成绩查询时间 2023年江苏专转本成绩查询时间预计在5月初，参加考试的考生，可以关注考试院发布的消息。江苏专转本考生可在规定时间内在省教育考试院网，在查询中心页面中输入准考证号和身份证号进行查询，或者拨…
阅读更多...
【u盘提示：驱动器未格式化】如何解决？

【u盘提示：驱动器未格式化】如何解决？

u盘虽然使用很方便，可随时拷贝资料到任何有电脑的地方，但它的问题也是比较多的，其中u盘提示驱动器未格式化故障最让人心虚，因为已经无法打开u盘了，里面的资料怎么办，很重要的怎么办，所以今天就教…
阅读更多...
LSSANet:一种用于肺结节检测的长、短切片感知网络

LSSANet:一种用于肺结节检测的长、短切片感知网络

文章目录 LSSANet: A Long Short Slice-Aware Network for Pulmonary Nodule Detection摘要方法Long Short Slice GroupingLong Short Slice-Aware Network 实验结果 LSSANet: A Long Short Slice-Aware Network for Pulmonary Nodule Detection 摘要 提出了一个长短片感知网…
阅读更多...
《Spring MVC》 第六章 MVC类型转换器、格式化器

《Spring MVC》 第六章 MVC类型转换器、格式化器

前言 介绍MVC类型转换器、格式化器 1、使用场景 <form th:action"{/user/register}" method"post">用户名&#xff1a;<input type"text" name"userName"/><br/>密码&#xff1a;<input type"password&q…
阅读更多...
【Access】win 10 / win 11：Access 下载、安装、使用教程（「管理信息系统」实践专用软件）

【Access】win 10 / win 11：Access 下载、安装、使用教程（「管理信息系统」实践专用软件）

目录 一、前言 二、卸载 Office 三、下载 Office Tool Plus 四、安装 Office&#xff08;内含 Access&#xff09; &#xff08;1&#xff09;启动 Office Tool Plus &#xff08;2&#xff09;部署 &#xff08;3&#xff09;安装 Office&#xff08;内含 Access&#…
阅读更多...
【Arduino SD卡和数据记录教程】

【Arduino SD卡和数据记录教程】

【Arduino SD卡和数据记录教程】 1. 前言2. 工作原理3. Arduino SD 卡模块代码4. Arduino SD卡数据记录1. 前言 在本Arduino教程中，我们将学习如何将SD卡模块与Arduino板一起使用。此外，结合DS3231实时时钟模块，我们将制作一个数据记录示例，将温度传感器的数据存储到SD卡中…
阅读更多...
Vue3技术7之toRaw与markRaw、customRef、provide与inject、响应式数据的判断、组合式API的优势分析

Vue3技术7之toRaw与markRaw、customRef、provide与inject、响应式数据的判断、组合式API的优势分析

Vue3技术7 toRaw与markRawtoRawApp.vueDemo.vue markRawDemo.vue 总结 customRefApp.vue总结 provide与inject目录结构App.vueChild.vueSon.vue总结 响应式数据的判断App.vue总结 组合式API的优势配置式的API存在的问题组合式API的优势 toRaw与markRaw toRaw App.vue <te…
阅读更多...
H7-TOOL的CANFD Trace全解析功能制作完成，历时一个月（2023-04-28）

H7-TOOL的CANFD Trace全解析功能制作完成，历时一个月（2023-04-28）

为了完成这个功能&#xff0c;差不多耗费了一个月时间&#xff0c;精神状态基本已经被磨平了。 当前已经支持&#xff1a; 1、LUA小程序控制&#xff0c;使用灵活。 2、采用SWD接口直接访问目标板芯片的CANFD外设寄存器和CANFD RAM区实现&#xff0c;支持USB&#xff0c;以太网…
阅读更多...
Paimon: Streaming data lake 数据湖项目的后起之秀

Paimon: Streaming data lake 数据湖项目的后起之秀

什么是Paimon? Paimon的官网介绍是&#xff1a;Streaming data lake platform with high-speed data ingestion, changelog tracking and efficient real-time analytics. Paimon 是流数据湖平台&#xff0c;具有高速数据摄取、变更日志跟踪和高效的实时分析能力 数据湖是大…
阅读更多...
最新文章

Copyright @ 2022~2023