一篇文章看懂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)