18.1 连接查询

关系型数据库通常采用规范化的设计方式，将不同的实体对象和它们之间的联系存储到多个表中。比如员工的个人信息存储在 employee 表中，部门相关的信息存储在 department 表中，同时 employee 表中存在一个外键字段（dept_id），引用了 department 表的主键字段。

因此，当我们想要查看员工的个人信息以及他/她所在的部门信息，就需要同时查询 employee 和 department 表中的信息。此时，我们需要使用连接查询。连接查询（join）可以基于两个表中的连接字段将数据行拼接到一起，返回两个表中的相关数据。

以下查询返回了人力资源部门中的所有员工信息：

select d.dept_id, 
       e.dept_id,
       dept_name,
       e.emp_name,
       e.sex
from employee e
join department d on e.dept_id = d.dept_id
where dept_name = '人力资源部';

其中， JOIN 表示连接查询，连接 employee 表和 department 表；ON 用于指定连接条件，这里表示 employee 中的部门编号等于 department 的部门编号。另外，查询语句中的 e 和 d 都是表的别名，当存在两个同名的字段时可以通过别名指定字段的来源。

该查询返回的结果如下：

dept_id|dept_id|dept_name    |emp_name   |sex|
-------|-------|-------------|-----------|---|
2|      2|人力资源部    |诸葛亮     |男  |
2|      2|人力资源部    |黄忠       |男  |
2|      2|人力资源部    |魏延       |男  |

以上示例使用 JOIN 和 ON 关键字指定表的连接查询，属于 ANSI SQL/92 标准语法；对于该查询，也可以使用 FROM 和 WHERE 实现如下：

select d.dept_id, 
       e.dept_id,
       dept_name,
       e.emp_name,
       e.sex
from employee e, department d
where dept_name = '人力资源部'
and e.dept_id = d.dept_id;

这种使用 FROM 和 WHERE 关键字指定表的连接属于 ANSI SQL/86 标准。

📝推荐使用 JOIN 和 ON 进行连接查询，它们的语义更清晰，更符合 SQL 的声明性；另外，当 WHERE 中包含多个查询条件，又用于指定表的连接关系时，会显得比较混乱。

MySQL 支持以下 SQL 连接查询：

• 内连接（INNER JOIN）；

• 左外连接（LEFT OUTER JOIN）；

• 右外连接（RIGHT OUTER JOIN）；

• 交叉连接（CROSS JOIN）；

• 自然连接（NATURAL JOIN）；

• 自连接（Self Join）。

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

18.2 内连接

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

其中，id = 1 和 id = 3 是两个表中匹配（ table1.id = table2.id ）的数据，因此内连接返回了 2 行记录。上文已经给出了内连接的示例，不再重复。

18.3 左外连接

左外连接返回左表中所有的数据行；对于右表，如果没有匹配的数据，显示为空值。左外连接使用关键字LEFT OUTER JOIN表示，也可以简写成LEFT JOIN。左外连接可以参考以下示意图（基于两个表的 id 进行等值连接）：

查询首先返回左表中的全部数据（id 等于 1、2、3）。由于 id = 2 在 table2 中不存在对应的数据，对于 table2 中的字段返回空值。

由于某些部门刚刚成立，可能还没有员工，因此前面的内连接查询不会显示这些部门的信息。如果想要在连接查询中返回这些部门的信息，需要使用左外连接：

select d.dept_id, e.dept_id, d.dept_name, e.emp_name
from department d
left join employee e on (e.dept_id = d.dept_id)
order by d.dept_id desc;

该查询的结果如下：

dept_id|dept_id|dept_name  |emp_name  |
-------|-------|-----------|----------|
6|       |保卫部      |          |
5|      5|销售部      |法正      |
5|      5|销售部      |庞统      |
5|      5|销售部      |蒋琬      |
5|      5|销售部      |黄权      |
...

从结果可以看出，“保卫部”目前还没有任何员工。

18.4 右外连接

右外连接返回右表中所有的数据行；对于左表，如果没有匹配的数据，显示为空值。右外连接使用关键字RIGHT OUTER JOIN表示，也可以简写成RIGHT JOIN；右外连接可以参考以下示意图（基于两个表的 id 进行等值连接）：

查询首先返回右表中的全部数据（id 等于 1、3、5）。由于 id = 5 在 table1 中不存在对应的数据，对于 table1 中的字段返回空值。
也就是说：

table1 RIGHT JOIN table2

等价于：

table2 LEFT JOIN table1

因此，上面的查询也可以使用右外连接来表示：

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

该语句返回的结果和上文中的左连接示例相同。

18.5 全外连接

SQL 全外连接等效于左外连接加上右外连接，返回左表和右表中所有的数据行。全外连接使用关键字FULL OUTER JOIN表示，也可以简写成FULL JOIN。全外连接的示意图如下（基于两个表的 id 进行连接）：

查询首先返回两个表中匹配的数据（id 等于 1 和 3，只返回一次）；对于 table1 中的 id = 2，table2 中的对应字段（price）显示为空；对于 table2 中的 id = 5，对应的 table1 中的字段（name）显示为空。

MySQL 目前还不支持全外连接，但是可以通过左/右外连接进行模拟。例如：

select *
from
(select 1 as id) t1
left join (select 2 as id) t2 on t1.id = t2.id;
id|id|
--|--|
1|  |

以上左连接查询返回了 t1 中的数据，如果换成右连接则只返回 t2 中的数据，使用以下语句可以同时返回两个表中的数据：

select *
from
(select 1 as id) t1
left join (select 2 as id) t2 on t1.id = t2.id
union all
select *
from
(select 1 as id) t1
right join (select 2 as id) t2 on t1.id = t2.id
where t1.id is null;

id|id|
--|--|
1|  |
| 2|

其中，union all 表示将两个查询的结果合并成一个更大的结果，我们在后续的集合运算符中进一步进行介绍。第二个 select 语句中的 where 条件用于排除两次查询中重复返回的数据。

对于外连接，需要注意WHERE条件和ON条件之间的差异：ON条件是针对连接之前的数据进行过滤，WHERE是针对连接之后的数据进行过滤，同一个条件放在不同的子句中可能会导致不同的结果。

以下示例将部门表与员工表进行左外连接查询，并且在 ON 子句中指定了多个条件：

select d.dept_id, e.dept_id, d.dept_name, e.emp_name
from department d
left join employee e on (e.dept_id = d.dept_id and e.emp_id = 0);

其中，ON 子句指定了一个不存在的员工（ 编号为 0），因此员工表不会返回任何数据。但是由于查询指定的是左外连接，仍然会返回部门信息，查询结果如下：

dept_id|dept_id|dept_name    |emp_name|
-------|-------|-------------|--------|
1|       |行政管理部    |        |
2|       |人力资源部    |        |
3|       |财务部       |        |
4|       |研发部       |        |
5|       |销售部       |        |
6|       |保卫部       |        |

对于相同的查询条件，使用 WHERE 子句的示例如下：

select d.dept_id, e.dept_id, d.dept_name, e.emp_name
from department d
left join employee e on (e.dept_id = d.dept_id)
where e.emp_id = 0;

该查询没有返回任何数据，因为左连接产生的结果经过 WHERE 条件（e.emp_id = 0）过滤之后没有任何满足的数据。

18.6 交叉连接

当连接查询没有指定任何连接条件时，就称为交叉连接。交叉连接使用关键字CROSS JOIN表示，也称为笛卡尔积（Cartesian product）。

两个表的笛卡尔积相当于一个表的所有行和另一个表的所有行两两组合，结果数量为两个表的行数相乘。假如第一个表有 100 行，第二个表有 200 行，它们的交叉连接将会产生 100 × 200 = 20000 行结果。交叉连接的示意图如下（基于两个表的 id 进行等值连接）：

18.7 自然连接

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

• 连接条件是等值连接，即 t1.col1 = t2.col1；

• 两个表中的列必须同名同类型，即 t1.col1 和 t2.col1 的类型相同。

select d.dept_id, 
       e.dept_id,
       dept_name,
       e.emp_name,
       e.sex
from employee e
join department d using (dept_id)
where dept_name = '人力资源部';

其中，USING 表示使用两个表中的公共字段（dept_id）进行等值连接。查询语句中的公共字段不需要添加表名限定。

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

select d.dept_id, 
       e.dept_id,
       dept_name,
       e.emp_name,
       e.sex
from employee e
natural join department d
where dept_name = '人力资源部';

自然连接并不是一种新的连接方式，只是特定情况下的简写形式，也可以用于简化外连接查询。

18.8 自连接

自连接（Self join）是指连接操作符的两边都是同一个表，即把一个表和它自己进行连接。自连接本质上并没有什么特殊之处，主要用于处理那些对自己进行了外键引用的表。

例如，员工表中的经理字段（manager）是一个外键列，指向了员工表自身的员工编号字段（emp_id）。如果要显示员工姓名以及他们经理的姓名，可以通过自连接实现：

select e.emp_name as "员工姓名",
       m.emp_name as "经理姓名"
from employee e
left join employee m on (m.emp_id = e.manager)
where e.dept_id = 1
order by e.emp_id;

员工姓名|经理姓名|
-------|--------|
刘备   |        |
关羽   |刘备    |
张飞   |刘备    |

该查询使用自连接关联了 2 次员工表，一个用于代表员工（e），另一个用于代表经理（m）；连接条件是经理的员工编号等于员工的经理编号。这种情况下，必须使用表别名才能区分两个员工表。该查询使用了左外连接，因为“刘备”是该公司的老板，他没有上级。

18.9 多表连接

如果我们还想要知道员工的职位信息，比如职位名称，可以在上文的连接查询中加上 job 表。以下示例使用连接查询返回了编号为 10 的员工所在的部门以及职位信息：

select e.emp_id, d.dept_name, e.emp_name, j.job_title
from employee e
join department d on (d.dept_id = e.dept_id)
join job j on (j.job_id = e.job_id)
where e.emp_id = 10;

emp_id|dept_name  |emp_name  |job_title   |
------|-----------|----------|------------|
10|研发部      |廖化      |程序员      |

其中，员工表和部门表使用部门编号进行连接，员工表和职位表使用职位编号进行连接。