SQL LEFT JOIN 是什么？怎么用？ – wiki基地

作者： /

深入理解 SQL LEFT JOIN：是什么？怎么用？一篇超详细的指南

在关系型数据库中，数据通常被分散存储在不同的、相互关联的表中。为了从这些表中整合信息，我们需要使用 SQL JOIN 操作。JOIN 语句根据两个或多个表之间的相关列，将这些表的行组合在一起。

SQL 中有多种类型的 JOIN：

  1. INNER JOIN (内连接): 返回在两个表中都有匹配关系的行。
  2. LEFT JOIN (左连接): 从左表（LEFT JOIN 关键字左边的表）中返回所有的行，即使在右表中没有匹配的行。
  3. RIGHT JOIN (右连接): 从右表（RIGHT JOIN 关键字右边的表）中返回所有的行，即使在左表中没有匹配的行。
  4. FULL OUTER JOIN (全外连接): 只要在任一表中有匹配，就会返回行。它会返回左右两表中所有的行，当某表中没有匹配时，对应列的值为 NULL。

本文将重点深入探讨 LEFT JOIN，包括它的核心概念、语法、详细的使用示例、与其它 JOIN 类型的比较，以及一些高级用法和注意事项。

一、SQL LEFT JOIN 的核心概念：它到底做了什么？

LEFT JOIN，也被称为 LEFT OUTER JOIN（在 SQL 标准中，LEFT JOIN 和 LEFT OUTER JOIN 是同义词，绝大多数数据库系统支持简写的 LEFT JOIN），它的主要作用是：

从 LEFT JOIN 关键字左边的表（我们通常称之为“左表”）中，获取所有的行。

同时，从 LEFT JOIN 关键字右边的表（我们称之为“右表”）中，只获取那些与左表中的行“匹配”的行。

如果左表中的某一行在右表中找不到任何匹配的行，那么结果集中依然会包含左表的这一行，而右表对应的列将显示 NULL 值。

用一个简单的类比来理解：想象你有两份名单。一份是班级所有学生的名单（左表），另一份是参加了学校运动会的学生名单和他们比赛成绩的名单（右表）。如果你想列出所有学生的信息，并且如果他们参加了运动会，就显示他们的成绩，那么你就需要用到 LEFT JOIN。

结果会包含所有学生的名字。对于参加了运动会的学生，你会看到他们的成绩信息；对于没有参加运动会的学生，他们的成绩列就会是空白（也就是 NULL）。

因此，LEFT JOIN 的核心目的是保留左表的所有信息，并尽可能地从右表补充相关信息。

二、SQL LEFT JOIN 的基本语法

LEFT JOIN 的基本语法如下：

sql
SELECT column_list
FROM table1 -- 这是左表 (LEFT table)
LEFT JOIN table2 -- 这是右表 (RIGHT table)
ON table1.column_name = table2.column_name
[WHERE condition];

让我们来分解一下这个语法：

  • SELECT column_list: 指定你希望从结果集中获取哪些列。这些列可以来自 table1，也可以来自 table2，或者两者兼有。
  • FROM table1: 指定作为“左表”的表。LEFT JOIN 将确保这个表中的所有行都出现在结果集中。
  • LEFT JOIN table2: 指定作为“右表”的表。LEFT JOIN 将尝试匹配 table1 中的行。
  • ON table1.column_name = table2.column_name: 这是连接条件。它指定了两个表之间是如何关联的。通常，这是一个等值条件，即两个表中具有相同值的某个列，表示它们是相关的。连接条件可以更复杂，包含多个列的比较，使用 AND 或 OR 连接。
  • [WHERE condition]: 这是可选的过滤条件。如果在 LEFT JOIN 之后使用 WHERE 子句，它会过滤掉已经连接好的结果集中的行。这与在 ON 子句中过滤数据有所不同（后面会详细解释）。

三、通过一个详细的示例理解 LEFT JOIN

为了更好地理解 LEFT JOIN 的工作原理，我们创建两个简单的表并插入一些数据。

假设我们有一个 Customers 表（存储客户信息）和一个 Orders 表（存储订单信息）。一个客户可能下了多个订单，也可能一个订单都没有下过。

表结构和数据：

Customers 表 (客户表)

CustomerID CustomerName City
1 Alice New York
2 Bob Los Angeles
3 Charlie Chicago
4 David Houston

Orders 表 (订单表)

OrderID CustomerID OrderDate Amount
101 1 2023-01-15 150.00
102 2 2023-01-18 200.00
103 1 2023-01-20 50.00
104 2 2023-01-22 300.00

注意：客户 Charlie (CustomerID 3) 和 David (CustomerID 4) 在 Orders 表中没有对应的订单记录。

我们的目标：

我们想列出所有的客户，以及他们所下的所有订单信息。即使客户没有下过订单，也应该在结果中显示他们的名字。

使用 LEFT JOIN 实现：

我们将 Customers 表作为左表，Orders 表作为右表，并使用 CustomerID 作为连接两个表的关联列。

sql
SELECT
c.CustomerID,
c.CustomerName,
c.City,
o.OrderID,
o.OrderDate,
o.Amount
FROM
Customers c -- c 是 Customers 表的别名
LEFT JOIN
Orders o ON c.CustomerID = o.CustomerID; -- o 是 Orders 表的别名

查询结果：

CustomerID CustomerName City OrderID OrderDate Amount
1 Alice New York 101 2023-01-15 150.00
1 Alice New York 103 2023-01-20 50.00
2 Bob Los Angeles 102 2023-01-18 200.00
2 Bob Los Angeles 104 2023-01-22 300.00
3 Charlie Chicago NULL NULL NULL
4 David Houston NULL NULL NULL

结果分析：

  1. 匹配的行: Alice (CustomerID 1) 和 Bob (CustomerID 2) 在 Orders 表中有匹配的记录。Alice 有两条订单记录 (101, 103)，Bob 也有两条 (102, 104)。LEFT JOIN 为每个匹配的订单创建了一行，并包含了 Customers 表和 Orders 表中对应的列信息。
  2. 不匹配的行: Charlie (CustomerID 3) 和 David (CustomerID 4) 在 Orders 表中没有任何订单记录。因为我们使用了 LEFT JOIN 并且 Customers 是左表，所以他们的信息仍然出现在结果集中。但是，来自右表 Orders 的列 (OrderID, OrderDate, Amount) 在这些行中显示为 NULL。

这个结果集完美地满足了我们的目标：列出了所有客户的信息，并附带了他们的订单详情（如果他们有订单的话）。

四、LEFT JOIN 与其它 JOIN 类型的比较

为了更好地理解 LEFT JOIN 的独特之处，我们简要比较一下它与 INNER JOIN、RIGHT JOIN 和 FULL OUTER JOIN 的结果差异，使用上面同样的 Customers 和 Orders 表数据。

  1. INNER JOIN:

    • SELECT c.CustomerName, o.OrderID FROM Customers c INNER JOIN Orders o ON c.CustomerID = o.CustomerID;
    • 结果: 只包含在两个表中都有匹配的行。 Charlie 和 David 将不会出现在结果中。
      | CustomerName | OrderID |
      | :———– | :—— |
      | Alice | 101 |
      | Alice | 103 |
      | Bob | 102 |
      | Bob | 104 |
    • 何时使用: 当你只关心那些在两个关联表中都存在对应记录的数据时。

  2. RIGHT JOIN:

    • SELECT c.CustomerName, o.OrderID FROM Customers c RIGHT JOIN Orders o ON c.CustomerID = o.CustomerID;
    • 结果: 返回右表 (Orders) 中的所有行，以及左表 (Customers) 中匹配的行。由于 Orders 表中的所有 CustomerID (1和2) 都在 Customers 表中有匹配，所以这个特定的例子结果与 INNER JOIN 相同。如果 Orders 表中有 CustomerID 不在 Customers 表中，那么左表 (Customers) 的列会显示 NULL。
      | CustomerName | OrderID |
      | :———– | :—— |
      | Alice | 101 |
      | Alice | 103 |
      | Bob | 102 |
      | Bob | 104 |
    • 何时使用: 当你主要关心右表的所有信息，并希望从左表补充信息时。 (LEFT JOIN 和 RIGHT JOIN 很多时候可以互换，只需要调换 FROM 和 JOIN 后面的表顺序即可)。

  3. FULL OUTER JOIN:

    • SELECT c.CustomerName, o.OrderID FROM Customers c FULL OUTER JOIN Orders o ON c.CustomerID = o.CustomerID;
    • 结果: 返回左表 (Customers) 和右表 (Orders) 中的所有行。如果在另一个表中没有匹配，则对应列显示 NULL。
      | CustomerName | OrderID |
      | :———– | :—— |
      | Alice | 101 |
      | Alice | 103 |
      | Bob | 102 |
      | Bob | 104 |
      | Charlie | NULL |
      | David | NULL |
    • 何时使用: 当你需要保留左表和右表的所有信息，并显示它们之间的所有可能匹配时。

通过比较可以看出，LEFT JOIN 的独特之处在于它强制保留左表的所有行，即使右表没有对应的匹配。这是它最主要的用途和特点。

五、LEFT JOIN 的高级用法和常见场景

LEFT JOIN 在实际应用中非常普遍，并且可以结合其他 SQL 子句实现更复杂的查询需求。

5.1 查询没有匹配的记录

这是 LEFT JOIN 一个非常常见的用途。通过 LEFT JOIN 两个表，然后过滤右表中那些因为没有匹配而导致 NULL 值的行，我们可以找到左表中那些在右表中不存在关联记录的行。

目标： 找出所有没有下过订单的客户。

sql
SELECT
c.CustomerID,
c.CustomerName,
c.City
FROM
Customers c
LEFT JOIN
Orders o ON c.CustomerID = o.CustomerID
WHERE
o.OrderID IS NULL; -- 过滤出右表列为 NULL 的行

查询结果：

CustomerID CustomerName City
3 Charlie Chicago
4 David Houston

分析： 这个查询首先执行 LEFT JOIN，得到我们前面看到的完整结果集（包含 Charlie 和 David 的 NULL 行）。然后，WHERE o.OrderID IS NULL 条件过滤掉了所有 OrderID 不为 NULL 的行（即那些有订单的客户和他们的订单详情），只剩下 OrderID 为 NULL 的行，这些行正好对应着没有下过订单的客户。

这种模式 (LEFT JOIN ... ON ... WHERE right_table_column IS NULL) 是查找“不匹配”或“缺失”数据的标准方法。

5.2 在 ON 子句中使用多个条件

连接条件 (ON 子句) 可以包含多个列或更复杂的逻辑。

目标： 找出所有客户以及他们在特定日期（例如，2023年1月）下的订单。

sql
SELECT
c.CustomerID,
c.CustomerName,
c.City,
o.OrderID,
o.OrderDate,
o.Amount
FROM
Customers c
LEFT JOIN
Orders o ON c.CustomerID = o.CustomerID
AND o.OrderDate BETWEEN '2023-01-01' AND '2023-01-31'; -- 在 ON 子句中添加日期条件

查询结果：

CustomerID CustomerName City OrderID OrderDate Amount
1 Alice New York 101 2023-01-15 150.00
1 Alice New York 103 2023-01-20 50.00
2 Bob Los Angeles 102 2023-01-18 200.00
2 Bob Los Angeles 104 2023-01-22 300.00
3 Charlie Chicago NULL NULL NULL
4 David Houston NULL NULL NULL

分析： 注意 ON 子句中的 AND o.OrderDate BETWEEN '2023-01-01' AND '2023-01-31'。这个条件不是过滤结果集，而是作为 JOIN 的匹配条件。只有当 c.CustomerID = o.CustomerID 并且 o.OrderDate 在指定范围内时，左右两行的记录才会被视为“匹配”。

  • 对于 Alice 和 Bob，他们的订单都在1月份，所以它们成功匹配并显示。
  • 对于 Charlie 和 David，他们在 Orders 表中没有任何记录，所以即使 ON 子句有额外的条件，他们仍然没有匹配，结果集依然包含他们的信息，右表列为 NULL。

重要提示：ON 子句 vs WHERE 子句 在 LEFT JOIN 中的区别

这是一个非常重要的概念：

  • ON 子句中的条件: 用于决定哪些行会被视为“匹配”并连接。如果右表的行不满足 ON 子句的条件，即使 CustomerID 匹配，它们也不会被连接进来。对于那些在左表有对应行但右表因为不满足 ON 条件而没有匹配的行，右表的列仍然会显示 NULL。ON 子句的过滤是在 JOIN 发生之前进行的（更准确地说，它定义了匹配的规则）。
  • WHERE 子句中的条件: 用于过滤已经完成 LEFT JOIN 的结果集。如果 WHERE 子句的条件引用了右表的列，并且这些列在 LEFT JOIN 之后是 NULL（因为没有匹配），那么 WHERE 条件通常不会通过（除非你用 IS NULL 检查），从而过滤掉这些行。WHERE 子句的过滤是在 JOIN 发生之后进行的。

看一个例子来对比：

例1：ON 中过滤 (已演示)
sql
-- 找出所有客户，以及他们在2023年1月的订单
SELECT ... FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID AND o.OrderDate BETWEEN ... ;
-- 结果包含所有客户，没下订单或1月没下订单的客户，右边列为 NULL

例2：WHERE 中过滤
sql
-- 找出所有在2023年1月下过订单的客户及订单详情
SELECT
c.CustomerID,
c.CustomerName,
c.City,
o.OrderID,
o.OrderDate,
o.Amount
FROM
Customers c
LEFT JOIN
Orders o ON c.CustomerID = o.CustomerID
WHERE
o.OrderDate BETWEEN '2023-01-01' AND '2023-01-31'; -- 在 WHERE 子句中过滤订单日期
查询结果：
| CustomerID | CustomerName | City | OrderID | OrderDate | Amount |
| :——— | :———– | :———- | :—— | :——— | :—– |
| 1 | Alice | New York | 101 | 2023-01-15 | 150.00 |
| 1 | Alice | New York | 103 | 2023-01-20 | 50.00 |
| 2 | Bob | Los Angeles | 102 | 2023-01-18 | 200.00 |
| 2 | Bob | Los Angeles | 104 | 2023-01-22 | 300.00 |
分析：
这个查询首先执行 c LEFT JOIN o ON c.CustomerID = o.CustomerID，生成了我们最初的完整结果集（包含 Charlie 和 David 的 NULL 行）。然后，WHERE o.OrderDate BETWEEN ... 条件作用于这个结果集。对于 Charlie 和 David 的行，o.OrderDate 是 NULL，NULL 不在指定的日期范围内，所以这两行被过滤掉了。结果只剩下那些在1月份有订单的客户及其订单信息。

总结差异：
* ON 子句中的右表过滤条件：不会阻止左表中不匹配的行出现在结果集中，只是右表的列会是 NULL。它影响的是 JOIN 的匹配过程。
* WHERE 子句中的右表过滤条件：会过滤掉 LEFT JOIN 结果集中右表列为 NULL 的行（假设条件不是 IS NULL）。它影响的是最终的结果集。

5.3 连接多个表

你可以连续使用 LEFT JOIN 来连接多个表。连接的顺序很重要，因为它决定了哪个表是左表。

目标： 找出所有客户，他们下的订单（如果有的），以及这些订单对应的产品信息（假设订单行项目在另一个 OrderItems 表中）。我们希望即使客户没有订单，或者订单没有对应的产品信息，客户的信息依然显示。

假设有一个 OrderItems 表：
OrderItems 表 (订单项表)
| OrderItemID | OrderID | ProductID | Quantity |
| :———- | :—— | :——– | :——- |
| 1 | 101 | P1 | 2 |
| 2 | 101 | P2 | 1 |
| 3 | 102 | P3 | 3 |
| 4 | 104 | P1 | 1 |

sql
SELECT
c.CustomerName,
o.OrderID,
oi.ProductID,
oi.Quantity
FROM
Customers c
LEFT JOIN
Orders o ON c.CustomerID = o.CustomerID -- 第一次 LEFT JOIN: Customers 和 Orders
LEFT JOIN
OrderItems oi ON o.OrderID = oi.OrderID; -- 第二次 LEFT JOIN: Orders 的结果集和 OrderItems

概念上的执行流程：
1. 先执行 Customers LEFT JOIN Orders ON ...，得到一个包含所有客户及其订单（或 NULL）的中间结果集。
2. 然后，将这个中间结果集作为新的“左表”，执行 LEFT JOIN OrderItems ON ...。它会尝试将中间结果集中的每一行与 OrderItems 表进行匹配。
* 对于中间结果集中那些 OrderID 不为 NULL 的行（即有订单的客户），它们会根据 OrderID 尝试匹配 OrderItems。如果一个订单有多个订单项，就会产生多行。
* 对于中间结果集中 OrderID 为 NULL 的行（即没有订单的客户，Charlie 和 David），它们无法与 OrderItems 表的任何行进行 o.OrderID = oi.OrderID 的匹配（NULL = anything 通常为 false），因此 OrderItems 的列会显示 NULL。

查询结果： (示例，取决于具体数据和连接过程)
| CustomerName | OrderID | ProductID | Quantity |
| :———– | :—— | :——– | :——- |
| Alice | 101 | P1 | 2 |
| Alice | 101 | P2 | 1 |
| Alice | 103 | NULL | NULL | — 假设订单103没有订单项
| Bob | 102 | P3 | 3 |
| Bob | 104 | P1 | 1 |
| Charlie | NULL | NULL | NULL |
| David | NULL | NULL | NULL |

分析： 结果集包含了所有客户。Alice 和 Bob 因为有订单，所以他们的订单信息被连接进来；接着，这些订单信息又尝试与 OrderItems 连接。Charlie 和 David 没有订单，在第一次 LEFT JOIN 后右表列为 NULL，第二次 LEFT JOIN 时也无法匹配，所以 OrderItems 的列也是 NULL。如果某个订单（如示例中的订单103）在 OrderItems 中没有匹配，那么该订单的行也会在第二次 LEFT JOIN 时导致 OrderItems 列为 NULL。

多表 LEFT JOIN 的关键是理解每个 LEFT JOIN 都在保留其当前左边的结果集的所有行，并尝试从右边匹配。

5.4 使用 LEFT JOIN 进行自连接 (Self-Join)

虽然 INNER JOIN 的自连接更常见（例如查找同一城市的客户），但 LEFT JOIN 的自连接也有其用途。例如，在一个员工表中，每个员工行都有一个 ManagerID 字段指向同一个表中的另一行（经理的 EmployeeID）。使用 LEFT JOIN 可以列出所有员工，并显示他们的经理名字，包括那些没有经理的员工（比如 CEO）。

假设 Employees 表：
| EmployeeID | EmployeeName | ManagerID |
| :——— | :———– | :——– |
| 1 | Alice (CEO) | NULL |
| 2 | Bob | 1 |
| 3 | Charlie | 1 |
| 4 | David | 2 |

目标： 列出所有员工及其经理的名字。

sql
SELECT
e.EmployeeName AS Employee,
m.EmployeeName AS Manager
FROM
Employees e -- 左表 (员工)
LEFT JOIN
Employees m ON e.ManagerID = m.EmployeeID; -- 右表 (经理)

查询结果：
| Employee | Manager |
| :——- | :—— |
| Alice | NULL |
| Bob | Alice |
| Charlie | Alice |
| David | Bob |

分析： 我们将 Employees 表自身连接，第一次出现时作为员工 (e)，第二次出现时作为经理 (m)。LEFT JOIN 以员工表 (e) 作为左表，确保所有员工都会出现在结果中。ON e.ManagerID = m.EmployeeID 条件尝试为每个员工找到其对应的经理行。对于 Alice，她的 ManagerID 是 NULL，无法与 m.EmployeeID 匹配，因此右表 (m，经理) 的列显示 NULL。对于其他员工，他们的 ManagerID 找到对应的经理行，从而显示经理的名字。

六、LEFT JOIN 的性能考虑

  • LEFT JOIN 通常比 INNER JOIN 的性能开销更大，因为它必须处理左表的所有行，即使这些行在右表中没有匹配。
  • 确保用于 JOIN 的列（在 ON 子句中的列）上建立了索引。这对于提高 JOIN 查询的性能至关重要，尤其是处理大量数据时。
  • 如果在 WHERE 子句中过滤右表的列（并且期望过滤掉 NULL 行），这实际上将 LEFT JOIN 的结果行为限制得更像 INNER JOIN，但执行计划可能仍然不同。理解过滤条件是在 JOIN 之前还是之后应用对于性能优化很重要。

七、LEFT JOIN 的最佳实践和技巧

  1. 始终使用表别名 (Aliases): 在复杂的查询中，给表起简短的别名（如 c for Customers, o for Orders）可以大大提高查询的可读性。
  2. 明确指定列属于哪个表: 在 SELECT 列表和 ON 子句中，始终使用 table_alias.column_name 的形式，避免歧义，即使列名在两个表中是唯一的。
  3. 理解 NULL 的含义: LEFT JOIN 结果中的 NULL 值表示在右表中没有找到匹配项。在后续处理或过滤结果时，要正确处理这些 NULL 值（例如使用 IS NULL 或 IS NOT NULL）。
  4. 选择正确的 JOIN 类型: 仔细思考你的查询目标。如果你只需要两个表中都有匹配的行，使用 INNER JOIN 通常更高效且语义更清晰。如果你需要保留左表的所有行，无论右表是否有匹配，那么 LEFT JOIN 是正确的选择。
  5. ON 子句的用途: 记住 ON 子句定义了如何进行匹配，它发生在概念上的 JOIN 过程之前。与 WHERE 子句作用于最终结果集不同。

八、总结

SQL LEFT JOIN 是数据库查询中一个非常强大且常用的工具。它的核心功能是保留左表的所有记录，并从右表补充匹配的信息。当右表中没有匹配项时，右表对应的列会填充 NULL 值。

掌握 LEFT JOIN 对于从多个相关表中有效地检索和组合数据至关重要，尤其是在需要包含“缺失”或“不匹配”数据的场景下（例如列出所有用户及其活动，即使某些用户没有活动；列出所有产品及其库存信息，即使某些产品没有库存）。

通过理解其基本概念、语法、通过示例实践，并注意 ON 与 WHERE 子句的区别以及性能考量，你可以自信地在各种数据库应用中使用 LEFT JOIN 来构建复杂的查询。

发表评论

您的邮箱地址不会被公开。 必填项已用 * 标注

滚动至顶部