MySQL内连接和外连接及七种SQL JOINS的实现

MySQL内连接、外连接及七种实现

更新时间：2023年3月23日 15:52:02 作者：子木君

本文主要介绍Mysql中内连接和外连接的区别以及七种实现方式。 相信看完这篇文章你就能够了解SQL内、外连接的多表查询了。 有需要的朋友可以参考以下

目录

1. 内连接

内连接：将两个或多个表中具有同一列的行进行合并，并且结果集中不包含一个表中与另一个表不匹配的行。

换句话说，查询结果只包含匹配的行，不匹配的行将被丢弃。

【示例】查询员工编号及其对应的部门名称。 其中，部门名称仅存在于部门表中，部门表如下图所示：

员工表和部门表通过部门编号匹配来连接。 查询代码如下所示：

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

搜索结果：

这里返回了106条记录，但是表一共有107条记录，少了1个人。 原因是表中存在一个部门号为(NULL)的员工，如下图所示：

但部门表中不存在值为（NULL）的部门号，因此该行不匹配的数据将被丢弃，不显示。 如下图所示，内连接只包含两个表的匹配行，也就是下图中两个圆相交的部分：

这种类型的连接称为内部连接。

2. 外连接

外连接：合并两个或多个表中具有同一列的行。 结果集除了查询一张表中与另一表匹配的行外，还会查询左表或右表中不匹配的行。

外连接分为三种类型：

左外连接：

两个表连接过程中，除了返回满足连接条件的行外，还返回左表中不满足条件的行。 如下图所示，左外连接是左边一整圈。

右外连接：

两个表连接过程中，除了返回满足连接条件的行外，还返回右表中不满足条件的行。 如下图所示，右外连接是右边的一整圈。

完全外连接：

两表连接过程中，除了返回满足连接条件的行外，还返回左右表中不满足条件的行。 如下图所示，完整的外连接是两个圆的所有部分。

【示例】根据部门编号，查询员工表中所有员工编号及其在部门表中对应的部门名称。

【分析】每当有要求查询标题中的所有单词时，我们必须支付12分，这意味着我们需要使用外连接查询。 可以使用SQL92和SQL99两种语法来实现外连接，具体参见[5.9常用SQL标准](#5.9常用SQL标准)。 由于左表中表有107条数据，而右表和左表之间只有106条数据匹配，所以需要进行左外连接。

【SQL92语法实现外连接】使用(+)。

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp, departments dept
WHERE emp.`department_id` = dept.`department_id`(+);

查询结果：错误

这是因为MySQL不支持SQL92语法的外连接操作。 但它是支持的。 所以说，学习没有白费。 MySQL仅支持SQL99语法来实现多表查询。

3、SQL99语法实现多表查询

SQL99是指SQL在1999年发布的SQL语法标准规范，虽然后来发布了一系列新的SQL标准，但是在学习MySQL的过程中，主要掌握SQL99和SQL92就足够了。 从本节开始，MySQL的学习就翻了一半，因为本节之前是SQL92语法，从本节开始，专门学习SQL99语法。

SQL99语法使用JOIN...ON实现多表查询，可以同时实现内连接和三种外连接。 MySQL 支持这种方法。

3.1 SQL99实现内连接

【例：三表查询】查询员工的工号、姓名、部门名称、城市。

【分析】这个需求需要三张表一起查询。

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`, loc.`city`
FROM employees emp JOIN departments dept
ON emp.`department_id` = dept.`department_id`
JOIN locations loc
ON dept.`location_id` = loc.`location_id`;

SQL99语法是添加表，只需JOIN一个表，在ON后添加连接条件。 注意这里JOIN前面省略了表示内连接的关键字INNER，使用内连接时可以忽略。 也就是说，代码也可以写成完整形式：

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`, loc.`city`
FROM employees emp INNER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
JOIN locations loc
ON dept.`location_id` = loc.`location_id`;

搜索结果：

3.2 SQL99语法实现外连接 3.2.1 左外连接

【示例】根据部门编号，查询员工表中所有员工编号及其在部门表中对应的部门名称。

【分析】由于左表是员工表，所以有107条数据； 右表是部门表，有27条数据。 题目要求是返回所有员工的107条查询结果，所以这里使用了左外连接。 在SQL99中实现左连接非常简单，只需在JOIN前添加两个关键字LEFT OUTER即可表示左外连接。 如下代码所示：

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`;

其中OUTER可以省略，即写为：

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT JOIN departments dept
ON emp.`department_id` = dept.`department_id`;

搜索结果：

3.2.2 右外连接

以此类推，右外连接就是在OUTER JOIN之前加一个关键字RIGHT。

SELECT emp.`employeed/master/img/d`;

搜索结果：

查询结果有122条记录，如何解释？ 回想一下右外连接的定义：

两个表连接过程中，除了返回满足连接条件的行外，还返回右表中不满足条件的行。 如下图所示，右外连接是右边的一整圈。

这并不难理解，因为右边没有人。 左右表（两个圆的交点）有106条匹配数据，所以总共有106 + 16 = 122 106+16=122 106+16=122条记录。 如下所示：

这个例子可以更好的帮助我们理解右外连接。

3.2.3 全外连接

打个比方，全外连接就是在OUTER JOIN之前加一个关键字FULL。 但不幸的是，MySQL不支持SQL99的完整外连接语法，但它是支持的。

在MySQL中，我们需要使用其他方法来实现全外连接，具体请参见4.6 全外连接。

4.总结：七种SQL JOINS的实现

在开始本节之前，您需要了解 SQL 中 UNION 和 UNION ALL 的定义和实现。 如果需要了解，可以阅读这篇博文：《MySQL 中 UNION 的使用》。

4.1 内连接

根据部门编号，查询员工表中的员工编号以及部门表中对应的部门名称。

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp JOIN departments dept
ON emp.`department_id` = dept.`department_id`;

搜索结果：

4.2 左外连接

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`;

搜索结果：

4.3 右外连接

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`;

4.4 第四种JOIN

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE dept.`department_id` IS NULL;

搜索结果：

功能是查询员工表中部门号为（NULL）的员工，如下图：

4.5 第五种JOIN

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`, emp.`department_id`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE emp.`department_id` IS NULL;

搜索结果：

4.6 全外连接

因为MySQL不支持SQL99语法中的全外连接。因此，我们的实现是求

4.2左外连接和4.5第五种JOIN可以组合UNION ALL； 或者4.3右外连接和4.4第四种JOIN可以组合UNION ALL，效果是一样的。

方法一

方法二

# 方法一
SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
UNION ALL
SELECT emp.`employee_id`, emp.`last_name`, dept.`department_id`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE emp.`department_id` IS NULL;
# 方法二
SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`employee_id` = dept.`department_id`
UNION ALL
SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`employee_id` = dept.`department_id`
WHERE dept.`department_id` IS NULL;

搜索结果：

4.7 第七种JOIN

要实现下面的操作，只需要找到4.4中第四种JOIN和4.5中第五种JOIN的UNION ALL即可。

SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp LEFT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE dept.`department_id` IS NULL
UNION ALL
SELECT emp.`employee_id`, emp.`last_name`, dept.`department_name`
FROM employees emp RIGHT OUTER JOIN departments dept
ON emp.`department_id` = dept.`department_id`
WHERE emp.`department_id` IS NULL;

搜索结果：

至此，这篇关于MySQL内、外连接以及七种SQL JOINS的实现的文章就介绍到这里了。 更多关于MySQL内、外连接的知识请搜索之前的文章 - 个人文章 - 思否