SQL 连接查询

连接查询使得 SQL 能够方便地通过一个查询获取多个表中的关联数据。本篇我们讨论内连接、左/右/全外连接、交叉连接、自然连接以及自连接的概念和作用。推荐使用语义更加清晰、更加通用的 JOIN 和 ON 语法实现连接查询。

本篇开始我们探讨一下如何从多个表中获取相关的数据。因为在关系数据库中，通常将不同的信息和它们之间的联系存储到多个表中。比如产品表、用户表、用户订单表、以及关联的订单明细表等。当我们想要查看某个订单时，需要同时从这几个表中查找关于订单的全部信息。

在 SQL 中，我们可以使用多表连接（JOIN）查询获取多个表中的关联数据。

在 SQL 的发展过程中，出现了两种连接查询的语法：

• ANSI SQL/86 标准，使用 FROM 和 WHERE 关键字指定表的连接。
• ANSI SQL/92 标准，使用 JOIN 和 ON 关键字指定表的连接；

在员工表（employee）中，存储了员工的信息和所在部门的编号；但是部门的信息存储在部门表（department）中。如果想要知道员工所在部门的名称，需要同时查询员工表和部门表。这个问题可以使用 FROM 和 WHERE 解决：

SELECT d.dept_id, e.dept_id, d.dept_name, e.emp_name
  FROM employee e, department d
 WHERE e.dept_id = d.dept_id;

其中，FROM 子句中的逗号用于连接两个表；同时在 WHERE 子句中指定了连接的条件是员工表中的部门编号（e.dept_id）等于部门表中的编号（d.dept_id）。另外，该查询中还通过别名（e 和 d）指定了查询的字段来自哪个表。该语句执行的结果如下（显示部分内容）：

对于同样的问题，我们看看如何使用 JOIN 和 ON 实现连接查询：

SELECT d.dept_id, e.dept_id, d.dept_name, e.emp_name
  FROM employee e
  JOIN department d ON (e.dept_id = d.dept_id);

首先，JOIN 表示需要连接员工表和部门表；ON 子句用于指定连接条件，同样是使用员工表中的部门编号和部门表中的编号进行相等连接。 通过使用部门编号进行连接，该查询既返回了员工的信息，又获得了员工所在部门的信息。该语句返回的结果与上面的示例相同。

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

接下来我们详细介绍一下 SQL 中的各种连接类型。

连接类型

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

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

内连接

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

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

左外连接

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

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

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

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;

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

查询的结果中包含一条“保卫部”的数据，它暂时还没有任何员工。

目前，只有 Oracle 实现了 ANSI SQL/86 标准的左外连接语法：

-- Oracle 实现
SELECT d.dept_id, e.dept_id, d.dept_name, e.emp_name
  FROM department d, employee e
 WHERE d.dept_id = e.dept_id(+)
 ORDER BY d.dept_id DESC;

其中，WHERE 条件右侧的操作符 (+) 表示右侧的表中可能缺少数据，也就是左外连接。该语句的结果与上面的示例相同。

右外连接

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

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

table1 RIGHT JOIN table2

等价于：

table2 LEFT JOIN table1

右外连接和左外连接可以相互转换。因此，上一节中的示例也可以使用等价的右外连接实现：

-- ANSI SQL/92 标准语法的右外连接
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);

-- ANSI SQL/86标准语法的右外连接
-- Oracle 实现
SELECT d.dept_id, e.dept_id, d.dept_name, e.emp_name
  FROM department d, employee e
 WHERE e.dept_id(+) = d.dept_id;

注意交换两个表的位置。以上语句的结果与上一节的示例相同。

全外连接

全外连接（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);

由于员工表的部门编号字段（dept_id）为非空字段，实际上不存在未分配部门的员工，该查询的结果与左外连接的示例相同。

MySQL不支持全外连接。 ANSI SQL/86 标准语法不支持全外连接。

交叉连接

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

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

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

结果总共包含 9 条记录。交叉连接一般使用较少。对于 ANSI SQL/86 标准的语法，交叉连接就是不指定表的连接条件。

除了上面介绍的几种连接类型，SQL 中还存在一些特殊形式的连接查询。

自然连接

对于连接查询，如果满足以下条件，可以使用 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 表示自然连接，这种情况下可以简化查询的输入。该语句的结果与上文中的内连接查询示例相同。

SQL Server 不支持自然连接。

自连接

自连接（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;

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

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

最后，我们来看一个同时连接多个表的查询示例。

多个表的连接

以下示例使用连接查询同时获取员工表、部门表以及职位表中的相关信息：

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.salary BETWEEN 10000 AND 15000
 ORDER BY e.emp_id;

emp_id|dept_name|emp_name|job_title|
------|---------|--------|---------|
     7|财务部    |孙尚香  |财务经理  |
     9|研发部    |赵云    |开发经理  |
    18|销售部    |法正    |销售经理  |

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