SQL-第 11 课。表的高级组合 (OUTER JOIN)

在上一节中，我们考虑了组合表的最简单方法 - 使用 WHERE 和 INNER JOIN 句子。这些组合称为内部组合或等价组合。然而，SQL 在其武器库中有更多用于连接表的选项，即还有其他类型的连接：外连接、自然连接和自连接。但首先我们要考虑一下如何为表指定别名，因为稍后我们将被迫使用字段的全名（Table.Field），如果没有缩写，由于长度过大，操作起来会非常困难。

1.表别名（Aliases）的使用

在上一节中，我们学习了如何使用别名来引用特定的表字段或计算字段。 SQL 还允许我们使用别名来代替表名。这给我们带来了一些优势，例如更短的 SQL 语法，并允许我们在 SELECT 语句中多次使用同一个表。

Run SQLSELECT Seller_name, SUM(Amount) AS Sum1 
FROM Sellers AS S, Sumproduct AS SP 
WHERE S.City = SP.City 
GROUP BY Seller_name

我们显示了每个卖家的已售商品总额。在我们的 SQL 查询中，我们使用了以下别名：对于计算字段 SUM(Amount)，别名为 Sum1；对于表 Sellers，别名为 S；对于 Sumproduct，别名为 SP。请注意，表别名也可以用在其他句子中，例如 ORDER BY、GROUP BY 等。

2.自连接(SELF JOIN)

让我们考虑一个例子。假设我们需要知道与 John Smith 在同一国家/地区进行交易的卖家的地址。为此，我们将创建以下请求：

Run SQLSELECT Address, City, Country, Seller_name
FROM Sellers
WHERE Country = (SELECT Country 
                 FROM Sellers 
                 WHERE Seller_name = 'John Smith')

另外，我们可以通过自连接的方式来解决这个问题，编写如下代码：

Run SQLSELECT S1.Address, S1.City, S1.Country, S1.Seller_name
FROM Sellers AS S1, Sellers AS S2 
WHERE S1.Country = S2.Country AND S2.Seller_name = 'John Smith'

为了解决这个问题，使用了化名。第一次为表 Sellers 分配别名 S1，第二次为别名 S2。这些别名可以用作表名。在 WHERE 运算符中，我们将前缀 S1 添加到每个字段的名称中，以便 DBMS 理解应该输出哪个表的字段（因为我们从一个表创建了两个虚拟表）。 WHERE 子句首先连接表，然后通过 Seller_name 字段过滤第二个表的数据，以仅返回必要的值。

自联接通常用于替换从与外部运算符 SELECT 相同的表中选择数据的子查询。尽管最终结果是相同的，但许多 DBMS 处理连接的速度比子查询快得多。值得尝试以确定哪个查询运行得更快。

3、自然组合

自然连接是一种仅选择那些不重复的列的连接。这通常是通过为一个表写入 (SELECT *) 并为其余表指定字段列表来完成的。例子：

Run SQLSELECT SP.*, S.Country
FROM Sumproduct AS SP, Sellers AS S 
WHERE SP.City = S.City

在此示例中，通配符 (*) 仅用于第一个表。所有其他列均已明确指定，因此不会选择重复的列。

4.外部组合(OUTER JOIN)

通常，合并时，一个表的行与另一个表的相应行链接，但是，在某些情况下，可能需要在结果中包含另一个表中没有相关行的行（即所有行）。选择一个表中的行并仅添加另一个表中的相关行）。这种类型的连接称为外部连接。为此，使用带有前缀 LEFT 或 RIGHT 的关键字 OUTER JOIN ... ON ...。

让我们考虑一个示例，之前已将一个新卖家 - Semuel Piter 添加到 Sellers 表中，该卖家尚未有销售额：

Run SQLSELECT Seller_name, SUM(Quantity) AS Qty
FROM Sellers2 
LEFT OUTER JOIN Sumproduct 
ON Sellers2.City = Sumproduct.City
GROUP BY Seller_name

根据此请求，我们提取了数据库中所有卖家的列表，并计算了他们所有月份销售的商品总额。我们看到新卖家 Semuel Piter 没有销售。如果我们使用内部联接，那么我们将看不到新卖家，因为他在 Sumproduct 表中没有记录。我们不仅可以通过写来改变组合的方向 LEFT 或 RIGHT，但也可以通过简单地更改表的顺序（即，以下两条记录将给出相同的结果： Sellers LEFT OUTER JOIN Sumproduct 和 Sumproduct RIGHT OUTER JOIN Sellers)。

此外，某些 DBMS 允许使用符号 *= 和 =* 在简化记录上进行外部连接，这分别对应于 LEFT OUTER JOIN 和 RIGHT OUTER JOIN。因此，前面的查询可以重写如下：

SELECT Seller_name, SUM(Quantity) AS Qty
FROM Sellers, Sumproduct
WHERE Sellers.City *= Sumproduct.City

不幸的是 MS Access 不支持外连接的缩短记录。

5. 全外连接（FULL OUTER JOIN）

还有另一种类型的外联接 - 完全外联接，它显示两个表中的所有行，并仅联接那些可以相关的行。完整外连接的语法如下：

SELECT Seller_name, Product
FROM Sellers 
FULL OUTER JOIN Sumproduct 
ON Sellers.City = Sumproduct.City

同样，以下 DBMS 不支持完全外连接：MS Access、MySQL、SQL Server 和 Sybase。如何解决这种不公正现象，我们将在下一节中考虑。