MySQL 学习笔记（15）— 连接查询（内连接、左外连接、右外连接、全外连接、交叉连接、自然连接等）

本文参考：https://gitbook.cn/gitchat/column/undefined/topic/5db92b68a9c3a53bc3800eff

SQL 支持的连接查询包括内连接、外连接、交叉连接、自然连接以及自连接等。其中，外连接又可以分为左外连接、右外连接以及全外连接。

另外，连接查询中的 ON 子句与 WHERE 子句类似，可以支持各种条件运算符（ = 、 >= 、 != 、 BETWEEN 等）。但最常用的是等值连接（ = ），我们主要介绍这种条件的连接查询。

假设我们有 3 张表， department 表 和 job 表内容分别为：

employee 表内容为：

1. 内连接

内连接（Inner Join）返回两个表中满足连接条件的数据；使用关键字 INNER JOIN 表示，也可以简写成 JOIN。内连接的原理如下图所示（基于两个表的 id 进行等值连接）：

其中，id = 1 和 id = 3 是两个表中匹配的数据，因此内连接返回了这 2 行记录。

例如：要查询所有员工的名称和所属的部门名称，就需要查询 employee 表和 department 表：

select e.emp_name, d.dept_name 
from employee as e
join department as d on (d.dept_id = e.dept_id)

首先， JOIN 表示需要连接员工表和部门表； ON 子句用于指定连接条件，同样是使用员工表中的部门编号和部门表中的编号进行相等连接。

2. 左外连接

左外连接（Left Outer Join）首先返回左表中所有的数据；对于右表，返回满足连接条件的数据；如果没有相应的数据就返回空值。左外连接使用关键字 LEFT OUTER JOIN 表示，也可以简写成 LEFT JOIN 。左外连接的原理如下图所示（基于两个表的 id 进行连接）：

其中，id = 2 的数据在 table1 中存在，在 table2 中不存在；左外连接仍然会返回左表中的该记录，而对于 table2 中的价格（price），返回的是空值。

假设我们想要查看所有的部门及其员工信息。考虑到某些部门可能还没有入职员工，如果使用内连接，则无法显示这些部门；因此使用左外连接：

select d.dept_name , e.emp_name
from department as d 
left join employee as e on (d.dept_id = e.dept_id)

其中， LEFT JOIN 表示左外连接；连接条件仍然是两个表中的部门编号是否相等。查询结果如下所示：

3. 右外连接

右外连接（Right Outer Join）首先返回右表中所有的数据；对于左表，返回满足连接条件的数据，如果没有相应的数据就返回空值。右外连接使用关键字 RIGHT OUTER JOIN 表示，也可以简写成 RIGHT JOIN 。右外连接的原理如下图所示（基于两个表的 id 进行连接）：

其中，id = 5 的数据在 table2 中存在，在 table1 中不存在；右外连接仍然会返回右表中的该记录，而对于 table1 中的名称（name），返回的是空值。简而言之：

table1 RIGHT JOIN table2

等价于：

table2 LEFT JOIN table1

右外连接和左外连接可以相互转换。

select d.dept_name , e.emp_name
from   employee as e
right join department as d  on (d.dept_id = e.dept_id)
order by emp_name

注意交换两个表的位置。以上语句的结果与左外连接的结果相同。

4. 全外连接

全外连接（Full Outer Join）等价于左外连接加上右外连接，同时返回左表和右表中所有的数据；对于两个表中不满足连接条件的数据返回空值。全外连接使用关键字 FULL OUTER JOIN 表示，也可以简写成 FULL JOIN 。全外连接的原理如下图所示（基于两个表的 id 进行连接）：

结果中包含了所有的 id，然后对于两个表中不满足连接条件的数据（id = 2 和 id = 5），分别在相应的字段中返回了空值。

假如我们想要查看所有的部门和员工信息。同时考虑到某些部门可能还没有员工，而且某些员工可能还没有分配部门，可以使用全外连接：

-- Oracle、SQL Server 以及 PostgreSQL 实现
SELECT d.dept_id, e.dept_id, d.dept_name, e.emp_name
  FROM department d
  FULL JOIN employee e ON (e.dept_id = d.dept_id);

MySQL 不支持全外连接。

5. 交叉连接

交叉连接也称为笛卡尔积（Cartesian Product），使用关键字 CROSS JOIN 表示。两个表的交叉连接相当于一个表的所有行和另一个表的所有行两两组合，结果的数量为两个表的行数相乘。如果第一个表有 1000 行，第二个表有 2000 行，它们的交叉连接将会产生 2000000 行数据。

交叉连接可能会导致查询结果的数量急剧增长，从而引起性能问题；通常应该使用连接条件进行过滤，避免产生交叉连接。

交叉连接的原理如下图所示（基于两个表的 id 进行连接）：

结果总共包含 9 条记录。交叉连接一般使用较少。

6. 自然连接

对于连接查询，如果满足以下条件，可以使用 USING 替代 ON 简化连接条件的输入：

• 连接条件是等值连接；
• 两个表中的连接字段必须名称相同，类型也相同。

针对上文中的内连接查询示例，可以使用 USING 简化如下：

-- Oracle、MySQL 以及 PostgreSQL 实现
SELECT dept_id, d.dept_name, e.emp_name
  FROM employee e
  JOIN department d USING (dept_id);

其中， USING 表示使用两个表中的公共字段（ dept_id ）进行等值连接。查询语句中的公共字段不需要添加表名限定。该语句的结果与上文中的内连接查询示例相同。

SQL Server 不支持 USING 语法， 不支持自然连接。

进一步来说，如果等值连接条件中包含了两个表中所有同名同类型的字段，可以使用自然连接（ NATURAL JOIN ）。例如，员工表和部门表只拥有 1 个同名字段 dept_id ；因此上面的示例可以使用自然连接表示：

-- Oracle、MySQL 以及 PostgreSQL 实现
SELECT dept_id, d.dept_name, e.emp_name
  FROM employee e
NATURAL JOIN department d;

NATURAL JOIN 表示自然连接，这种情况下可以简化查询的输入。该语句的结果与上文中的内连接查询示例相同。