跳到主要内容

SQL 外连接

在 SQL 中，表连接（Join）是将两个或多个表中的数据组合在一起的操作。外连接（Outer Join）是其中一种重要的连接类型，它允许我们在查询结果中包含不匹配的行。这对于分析数据中的缺失信息或部分匹配的情况非常有用。

什么是外连接？​

外连接是一种表连接操作，它不仅返回匹配的行，还返回至少一个表中没有匹配的行。外连接分为三种类型：

  1. 左外连接（LEFT OUTER JOIN）：返回左表中的所有行，即使右表中没有匹配的行。
  2. 右外连接（RIGHT OUTER JOIN）：返回右表中的所有行，即使左表中没有匹配的行。
  3. 全外连接（FULL OUTER JOIN）：返回左表和右表中的所有行，即使没有匹配的行。
备注

外连接的关键在于“保留”未匹配的行。左外连接保留左表的未匹配行，右外连接保留右表的未匹配行，全外连接则保留两表的未匹配行。

左外连接（LEFT OUTER JOIN）​

左外连接是最常用的外连接类型。它返回左表中的所有行，以及右表中与左表匹配的行。如果右表中没有匹配的行，则结果中右表的列将显示为 NULL。

语法​

SELECT 列名
FROM 左表
LEFT OUTER JOIN 右表
ON 左表.列名 = 右表.列名;

示例​

假设我们有两个表：Customers 和 Orders。

-- Customers 表
+----+----------+
| ID | Name     |
+----+----------+
| 1  | Alice    |
| 2  | Bob      |
| 3  | Charlie  |
+----+----------+

-- Orders 表
+----+------------+-------+
| ID | OrderDate  | Amount|
+----+------------+-------+
| 1  | 2023-01-01 | 100   |
| 3  | 2023-01-03 | 200   |
+----+------------+-------+

我们想要查询所有客户及其订单信息，即使某些客户没有订单。

SELECT Customers.Name, Orders.OrderDate, Orders.Amount
FROM Customers
LEFT OUTER JOIN Orders
ON Customers.ID = Orders.ID;

输出​

+----------+------------+--------+
| Name     | OrderDate  | Amount |
+----------+------------+--------+
| Alice    | 2023-01-01 | 100    |
| Bob      | NULL       | NULL   |
| Charlie  | 2023-01-03 | 200    |
+----------+------------+--------+
提示

左外连接非常适合查找“缺失”的数据。例如，在上面的例子中，我们可以轻松找到没有订单的客户（Bob）。

右外连接（RIGHT OUTER JOIN）​

右外连接与左外连接类似，但它返回右表中的所有行，以及左表中与右表匹配的行。如果左表中没有匹配的行，则结果中左表的列将显示为 NULL。

语法​

SELECT 列名
FROM 左表
RIGHT OUTER JOIN 右表
ON 左表.列名 = 右表.列名;

示例​

继续使用 Customers 和 Orders 表，我们想要查询所有订单及其对应的客户信息，即使某些订单没有对应的客户。

SELECT Customers.Name, Orders.OrderDate, Orders.Amount
FROM Customers
RIGHT OUTER JOIN Orders
ON Customers.ID = Orders.ID;

输出​

+----------+------------+--------+
| Name     | OrderDate  | Amount |
+----------+------------+--------+
| Alice    | 2023-01-01 | 100    |
| Charlie  | 2023-01-03 | 200    |
+----------+------------+--------+
警告

右外连接在实际应用中较少使用，因为通常我们会将主表放在左边。但在某些情况下，它仍然非常有用。

全外连接（FULL OUTER JOIN）​

全外连接返回左表和右表中的所有行。如果某一行在另一个表中没有匹配的行，则结果中对应的列将显示为 NULL。

语法​

SELECT 列名
FROM 左表
FULL OUTER JOIN 右表
ON 左表.列名 = 右表.列名;

示例​

假设我们想要查询所有客户和订单信息，包括没有订单的客户和没有客户的订单。

SELECT Customers.Name, Orders.OrderDate, Orders.Amount
FROM Customers
FULL OUTER JOIN Orders
ON Customers.ID = Orders.ID;

输出​

+----------+------------+--------+
| Name     | OrderDate  | Amount |
+----------+------------+--------+
| Alice    | 2023-01-01 | 100    |
| Bob      | NULL       | NULL   |
| Charlie  | 2023-01-03 | 200    |
| NULL     | 2023-01-05 | 300    | -- 假设有一个没有客户的订单
+----------+------------+--------+
注意

并非所有数据库系统都支持全外连接。例如，MySQL 不支持 FULL OUTER JOIN，但可以通过 UNION 实现类似的效果。

实际应用场景​

场景 1：查找没有订单的客户​

使用左外连接，我们可以轻松找到没有订单的客户。

SELECT Customers.Name
FROM Customers
LEFT OUTER JOIN Orders
ON Customers.ID = Orders.ID
WHERE Orders.ID IS NULL;

场景 2：查找没有客户的订单​

使用右外连接，我们可以找到没有客户的订单。

SELECT Orders.OrderDate, Orders.Amount
FROM Customers
RIGHT OUTER JOIN Orders
ON Customers.ID = Orders.ID
WHERE Customers.ID IS NULL;

总结​

外连接是 SQL 中非常强大的工具，它允许我们在查询结果中包含不匹配的行。通过左外连接、右外连接和全外连接，我们可以灵活地处理各种数据查询需求。

  • 左外连接：保留左表中的所有行。
  • 右外连接：保留右表中的所有行。
  • 全外连接：保留两个表中的所有行。

附加资源与练习​

  1. 练习：尝试在数据库中创建两个表，并使用外连接查询数据。
  2. 进一步学习：了解如何在 MySQL 中通过 UNION 实现全外连接的效果。
提示

外连接是数据分析中的重要工具，掌握它将帮助你更好地理解和处理复杂的数据关系。