SQL 如何使用自联结、自然联结和外联结「终于解决」

本文介绍自联结（self-join）、自然联结（natural join）和外联结 （outer join），包括它们的含义和使用方法。介绍如何使用表别名，如何对被联结的表使用聚集函数。

一、使用表别名

SQL 如何创建计算字段 介绍了如何使用别名引用被检索的表列。给列起别名的语法如下：

SELECT RTRIM(vend_name) + " (" + RTRIM(vend_country) + ")"
        AS vend_title
FROM Vendors
ORDER BY vend_name;

SQL 除了可以对列名和计算字段使用别名，还允许给表名起别名。这样做有两个主要理由：

• 缩短 SQL 语句；
• 允许在一条 SELECT 语句中多次使用相同的表。

请看下面的 SELECT 语句。它与 如何使用 SQL INNER JOIN 联结两个或多个表 例子中所用的语句基本相同，但改成了使用别名：

SELECT cust_name, cust_contact
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
 AND OI.order_num = O.order_num
 AND prod_id = "RGAN01";

可以看到，FROM 子句中的三个表全都有别名。Customers AS C 使用 C 作为 Customers 的别名，如此等等。

这样，就可以使用省略的 C 而不用全名 Customers。

在这个例子中，表别名只用于 WHERE 子句。其实它不仅能用于 WHERE 子句，还可以用于 SELECT 的列表、ORDER BY 子句以及其他语句部分。

注意：Oracle 中没有 AS

Oracle 不支持 AS 关键字。

要在 Oracle 中使用别名，可以不用 AS，简单地指定列名即可（因此，应该是 Customers C，而不是 Customers AS C）。

需要注意，表别名只在查询执行中使用。与列别名不一样，表别名不返回到客户端。

二、使用不同类型的联结

迄今为止，我们使用的只是内联结或等值联结的简单联结。现在来看三种其他联结：自联结（self-join）、自然联结（natural join）和外联结 （outer join）。

2.1 自联结

如前所述，使用表别名的一个主要原因是能在一条 SELECT 语句中不止一次引用相同的表。下面举一个例子。

假如要给与 Jim Jones 同一公司的所有顾客发送一封信件。这个查询要求首先找出 Jim Jones 工作的公司，然后找出在该公司工作的顾客。

下面是解决此问题的一种方法：

SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
                   FROM Customers
                   WHERE cust_contact = "Jim Jones");

输出：

cust_id      cust_name          cust_contact
--------     --------------     --------------
1000000003   Fun4All            Jim Jones
1000000004   Fun4All            Denise L. Stephens

这是第一种解决方案，使用了子查询。内部的 SELECT 语句做了一个简单检索，返回 Jim Jones 工作公司的 cust_name。

该名字用于外部查询的 WHERE 子句中，以检索出为该公司工作的所有雇员（SQL 如何使用子查询 中介绍了子查询，更多信息请参阅此文章）。

现在来看使用联结的相同查询：

SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
 AND c2.cust_contact = "Jim Jones";

输出：

cust_id     cust_name       cust_contact
-------     -----------     --------------
1000000003  Fun4All         Jim Jones
1000000004  Fun4All         Denise L. Stephens

提示：Oracle 中没有 AS

Oracle 用户应该记住去掉 AS。

此查询中需要的两个表实际上是相同的表，因此 Customers 表在 FROM 子句中出现了两次。

虽然这是完全合法的，但对 Customers 的引用具有歧义性，因为 DBMS 不知道你引用的是哪个 Customers 表。

解决此问题，需要使用表别名。Customers 第一次出现用了别名 c1，第二次出现用了别名 c2。现在可以将这些别名用作表名。

例如，SELECT 语句使用 c1 前缀明确给出所需列的全名。

如果不这样，DBMS 将返回错误，因为名为 cust_id、cust_name、cust_contact 的列各有两个。DBMS 不知道想要的是哪一列（即使它们其实是同一列）。

WHERE 首先联结两个表，然后按第二个表中的 cust_contact 过滤数据，返回所需的数据。

提示：用自联结而不用子查询

自联结通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句。

虽然最终的结果是相同的，但许多 DBMS 处理联结远比处理子查询快得多。

应该试一下两种方法，以确定哪一种的性能更好。

2.2 自然联结

无论何时对表进行联结，应该至少有一列不止出现在一个表中（被联结的列）。

标准的联结（如何使用 SQL INNER JOIN 联结两个或多个表 中介绍的内联结）返回所有数据，相同的列甚至多次出现。

自然联结排除多次出现，使每一列只返回一次。

怎样完成这项工作呢？答案是，系统不完成这项工作，由你自己完成它。

自然联结要求你只能选择那些唯一的列，一般通过对一个表使用通配符（SELECT *），而对其他表的列使用明确的子集来完成。下面举一个例子：

SELECT C.*, O.order_num, O.order_date,
       OI.prod_id, OI.quantity, OI.item_price
FROM Customers AS C, Orders AS O,
     OrderItems AS OI
WHERE C.cust_id = O.cust_id
 AND OI.order_num = O.order_num
 AND prod_id = "RGAN01";

提示：Oracle 中没有 AS

Oracle 用户应该记住去掉 AS。

在这个例子中，通配符只对第一个表使用。所有其他列明确列出，所以没有重复的列被检索出来。

事实上，我们迄今为止建立的每个内联结都是自然联结，很可能永远都不会用到不是自然联结的内联结。

2.3 外联结

许多联结将一个表中的行与另一个表中的行相关联，但有时候需要包含没有关联行的那些行。例如，可能需要使用联结完成以下工作：

• 对每个顾客下的订单进行计数，包括那些至今尚未下订单的顾客；
• 列出所有产品以及订购数量，包括没有人订购的产品；
• 计算平均销售规模，包括那些至今尚未下订单的顾客。

在上述例子中，联结包含了那些在相关表中没有关联行的行。这种联结称为外联结。

注意：语法差别

需要注意，用来创建外联结的语法在不同的 SQL 实现中可能稍有不同。

下面段落中描述的各种语法形式覆盖了大多数实现，在继续学习之前请参阅你使用的 DBMS 文档，以确定其语法。

下面的 SELECT 语句给出了一个简单的内联结。它检索所有顾客及其订单：

SELECT Customers.cust_id, Orders.order_num
FROM Customers
 INNER JOIN Orders ON Customers.cust_id = Orders.cust_id;

外联结语法类似。要检索包括没有订单顾客在内的所有顾客，可如下进行：

SELECT Customers.cust_id, Orders.order_num
FROM Customers
 LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

输出：

cust_id        order_num
----------     ---------
1000000001     20005
1000000001     20009
1000000002     NULL
1000000003     20006
1000000004     20007
1000000005     20008

类似 如何使用 SQL INNER JOIN 联结两个或多个表 提到的内联结，这条 SELECT 语句使用了关键字 OUTER JOIN 来指定联结类型（而不是在 WHERE 子句中指定）。

但是，与内联结关联两个表中的行不同的是，外联结还包括没有关联行的行。

在使用 OUTER JOIN 语法时，必须使用 RIGHT 或 LEFT 关键字指定包括其所有行的表（RIGHT 指出的是 OUTER JOIN 右边的表，而 LEFT 指出的是 OUTER JOIN 左边的表）。

上面的例子使用 LEFT OUTER JOIN 从 FROM 子句左边的表（Customers 表）中选择所有行。

为了从右边的表中选择所有行，需要使用 RIGHT OUTER JOIN，如下例所示：

SELECT Customers.cust_id, Orders.order_num
FROM Customers
 RIGHT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

注意：SQLite 外联结

SQLite 支持 LEFT OUTER JOIN，但不支持 RIGHT OUTER JOIN。

幸好，如果你确实需要在 SQLite 中使用 RIGHT OUTER JOIN，有一种更简单的办法，这将在下面的提示中介绍。

提示：外联结的类型

要记住，总是有两种基本的外联结形式：左外联结和右外联结。

它们之间的唯一差别是所关联的表的顺序。

换句话说，调整 FROM 或 WHERE 子句中表的顺序，左外联结可以转换为右外联结。

因此，这两种外联结可以互换使用，哪个方便就用哪个。

还存在另一种外联结，就是全外联结（full outer join），它检索两个表中的所有行并关联那些可以关联的行。

与左外联结或右外联结包含一个表的不关联的行不同，全外联结包含两个表的不关联的行。全外联结的语法如下：

SELECT Customers.cust_id, Orders.order_num
FROM Customers
 FULL OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;

注意：FULL OUTER JOIN 的支持

MariaDB、MySQL 和 SQLite 不支持 FULL OUTER JOIN 语法。

三、使用带聚集函数的联结

如 如何使用 SQL AVG、COUNT、MAX、MIN 和 SUM 汇总数据 所述，聚集函数用来汇总数据。

虽然至今为止我们举的聚集函数的例子都只是从一个表中汇总数据，但这些函数也可以与联结一起使用。

我们来看个例子，要检索所有顾客及每个顾客所下的订单数，下面的代码使用 COUNT() 函数完成此工作：

SELECT Customers.cust_id,
       COUNT(Orders.order_num) AS num_ord
FROM Customers
 INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

输出：

cust_id        num_ord
----------     --------
1000000001     2
1000000003     1
1000000004     1
1000000005     1

这条 SELECT 语句使用 INNER JOIN 将 Customers 和 Orders 表互相关联。

GROUP BY 子句按顾客分组数据，因此，函数调用 COUNT(Orders.order_num) 对每个顾客的订单计数，将它作为 num_ord 返回。

聚集函数也可以方便地与其他联结一起使用。请看下面的例子：

SELECT Customers.cust_id,
       COUNT(Orders.order_num) AS num_ord
FROM Customers
 LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id
GROUP BY Customers.cust_id;

输出：

cust_id        num_ord
----------     -------
1000000001     2
1000000002     0
1000000003     1
1000000004     1
1000000005     1

这个例子使用左外部联结来包含所有顾客，甚至包含那些没有任何订单的顾客。结果中也包含了顾客 1000000002，他有 0 个订单，这和使用 INNER JOIN 时不同。

四、使用联结和联结条件

在总结讨论联结的这两篇前，有必要汇总一下联结及其使用的要点。

• 注意所使用的联结类型。一般我们使用内联结，但使用外联结也有效。
• 关于确切的联结语法，应该查看具体的文档，看相应的 DBMS 支持何种语法（大多数 DBMS 使用这两篇中描述的某种语法）。
• 保证使用正确的联结条件（不管采用哪种语法），否则会返回不正确的数据。
• 应该总是提供联结条件，否则会得出笛卡儿积。
• 在一个联结中可以包含多个表，甚至可以对每个联结采用不同的联结类型。虽然这样做是合法的，一般也很有用，但应该在一起测试它们前分别测试每个联结。这会使故障排除更为简单。

五、小结

本文是 如何使用 SQL INNER JOIN 联结两个或多个表 的延续，首先介绍了如何以及为什么使用别名，然后讨论不同的联结类型以及每类联结所使用的语法。

我们还介绍了如何与联结一起使用聚集函数，以及在使用联结时应该注意的问题。

原文链接：https://www.developerastrid.com/sql/sql-joins/

（完）