跳到主要内容

SQL 外连接

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

什么是外连接?

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

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

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

左外连接(LEFT OUTER JOIN)

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

语法

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

示例

假设我们有两个表:CustomersOrders

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

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

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

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

输出

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

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

右外连接(RIGHT OUTER JOIN)

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

语法

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

示例

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

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

输出

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

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

全外连接(FULL OUTER JOIN)

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

语法

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

示例

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

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

输出

sql
+----------+------------+--------+
| 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:查找没有订单的客户

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

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

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

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

sql
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 实现全外连接的效果。
提示

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