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

1. 内连接

2.外连接左外连接：右外连接：满外连接：

3. SQL99语法实现多表查询

3.1 SQL99实现内连接

3.2 SQL99语法实现外连接

3.2.1 左外连接3.2.2 右外连接

3.2.3 满外连接

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

4.1 内连接

4.2 左外连接

4.3 右外连接

4.4 第四种JOIN

4.5 第五种JOIN

4.6 满外连接方法一方法二

4.7 第七种JOIN

1. 内连接

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

说人话就是，查询结果只包含它们匹配的行，不匹配的就不要了。

【例子】查询员工编号 employee_id 和其对应的部门名称 department_name 。其中部门名称 department_name 只在部门表 departments 中，部门表 departments 如下图所示：

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

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

查询结果：

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

而部门表 departments 中却没有值为 (NULL) 的部门编号 department_id ，因此这一行不匹配的数据就被丢弃不显示了。如下图所示，内连接只包含两个表匹配的行，即下图中两圆相交的部分：

这种连接方式称作内连接。

2.外连接

外连接：合并具有同一列的两个以上的表的行，结果集中除了包含一个表与另一个表匹配的行之外，还查询到了左表或右表中不匹配的行。

外连接又分为以下三类：

左外连接：

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

右外连接：

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

满外连接：

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

【例子】根据部门编号 department_id ，查询员工表 employees 中的所有员工编号 employee_id 和部门表 departments 中其对应的部门名称 department_name 。

【分析】凡是题目中出现要求查询 所有 的字眼时，都要打起十二分精神，这说明需要我们使用外连接查询。实现外连接可使用SQL92和SQL99两种语法，详见[5.9 常用的SQL标准](# 5.9 常用的SQL标准) 。由于左表员工表 employees 共有 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语法的外连接操作。但是Oracle是支持的。所以没有白学。MySQL只支持SQL99语法来实现多表查询。

3. SQL99语法实现多表查询

SQL99是指SQL在1999年颁布的SQL语法标准规范。尽管在之后发布了一系列新的SQL标准，但在学习MySQL的过程中，主要掌握SQL99和SQL92就已经足够。从这节开始，MySQL的学习就算翻了半篇了，因为这一节之前都是SQL92语法，从这节开始，就专为SQL99语法。

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

3.1 SQL99实现内连接

【例子：三表查询】查询员工的员工编号 employee_id 、姓名 last_name 、部门名称 department_name 和所在城市 city 。

【分析】这个需求需要 3 张表共同查询。

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 左外连接

【例子】根据部门编号 department_id ，查询员工表 employees 中的所有员工编号 employee_id 和部门表 departments 中其对应的部门名称 department_name 。

【分析】由于左表是员工表 employees ，有107条数据；而右表是部门表 departments ，有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的满外连接语法，Oracle是支持的。

我们需要使用别的方法实现MySQL中的满外连接，详见4.6 满外连接。

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

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

4.1 内连接

根据部门编号 department_id ，查询员工表 employees 中的员工编号 employee_id 和部门表 departments 中其对应的部门名称 department_name 。

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;

查询结果：

作用是把员工表 employees 中，部门编号 department_id 为 (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;

查询结果：