sql-leftjoin-vs-rightjoin

SQL 外连接：LEFT JOIN vs. RIGHT JOIN

当从多个表查询数据时，我们经常需要处理一个表中的记录在另一个表中没有对应匹配项的情况。这时 OUTER JOIN（外连接）就至关重要。LEFT JOIN（左连接）和 RIGHT JOIN（右连接）是两种最常见的外连接类型。它们允许您从一个表中检索所有记录，以及来自第二个表的匹配记录。如果未找到匹配项，则第二个表的列将返回 NULL 值。

LEFT JOIN 如何工作

LEFT JOIN（或 LEFT OUTER JOIN）返回 左表（第一个提及的表）中的所有记录，以及右表中的匹配记录。如果左表中的记录在右表中没有匹配项，结果仍将包含该记录，但右表中的所有列都将显示 NULL 值。

语法

SELECT
    t1.column1, t1.column2,
    t2.column1, t2.column2
FROM
    table1 AS t1
LEFT JOIN
    table2 AS t2 ON t1.common_column = t2.common_column;

概念示例

让我们考虑两个表：employees 和 departments。departments 表列出了所有正式部门，但有些员工可能尚未分配到部门。

employees 表：

+----+-----------+---------------+---------------+
| id | name      | department_id | hire_date     |
+----+-----------+---------------+---------------+
| 1  | Alice     | 101           | 2022-01-15    |
| 2  | Bob       | 102           | 2022-03-20    |
| 3  | Charlie   | 101           | 2023-05-10    |
| 4  | David     | NULL          | 2023-08-01    |
+----+-----------+---------------+---------------+

departments 表：

+-----+-------------+
| id  | name        |
+-----+-------------+
| 101 | Engineering |
| 102 | Marketing   |
| 103 | Sales       |
+-----+-------------+

如果我们想要一个包含 所有员工 及其部门名称的列表，我们使用 LEFT JOIN，并将 employees 作为左表。

SELECT e.name, d.name AS department_name
FROM employees AS e
LEFT JOIN departments AS d ON e.department_id = d.id;

结果： 请注意，David 被包含了，但他的 department_name 为 NULL，因为他的 department_id 为 NULL，并且与 departments 表中的任何 ID 都不匹配。

+-----------+-----------------+
| name      | department_name |
+-----------+-----------------+
| Alice     | Engineering     |
| Bob       | Marketing       |
| Charlie   | Engineering     |
| David     | NULL            |
+-----------+-----------------+

RIGHT JOIN 如何工作

RIGHT JOIN（或 RIGHT OUTER JOIN）是 LEFT JOIN 的镜像。它返回 右表（第二个提及的表）中的所有记录，以及左表中的匹配记录。如果右表中的记录在左表中没有匹配项，结果仍将包含该记录，但左表中的所有列都将显示 NULL 值。

语法

SELECT
    t1.column1, t1.column2,
    t2.column1, t2.column2
FROM
    table1 AS t1
RIGHT JOIN
    table2 AS t2 ON t1.common_column = t2.common_column;

概念示例

使用相同的表，假设我们想要一个包含 所有部门 及其员工姓名的列表。我们使用 RIGHT JOIN，并将 departments 作为右表。

SELECT e.name, d.name AS department_name
FROM employees AS e
RIGHT JOIN departments AS d ON e.department_id = d.id;

结果： 请注意，‘Sales’部门被包含了，但员工 name 为 NULL，因为没有员工被分配到部门 ID 103。

+---------+-----------------+
| name    | department_name |
+---------+-----------------+
| Alice   | Engineering     |
| Charlie | Engineering     |
| Bob     | Marketing       |
| NULL    | Sales           |
+---------+-----------------+

主要区别和最佳实践

根本区别在于哪个表的记录被完全保留。LEFT JOIN 保留左表的行，而 RIGHT JOIN 保留右表的行。

特性	LEFT JOIN	RIGHT JOIN
主表	JOIN 关键字左侧的表	JOIN 关键字右侧的表
不匹配的行	包含左表中的所有行，即使没有匹配项。	包含右表中的所有行，即使没有匹配项。
NULL 出现位置	NULL 出现在右表的列中。	NULL 出现在左表的列中。

最佳实践：优先使用 LEFT JOIN

任何 RIGHT JOIN 都可以通过简单地交换表的顺序来重写为 LEFT JOIN。例如，上面的 RIGHT JOIN 查询等效于：

SELECT e.name, d.name AS department_name
FROM departments AS d
LEFT JOIN employees AS e ON d.id = e.department_id;

许多开发团队都采用只使用 LEFT JOIN 的约定。这提高了代码的可读性和一致性，因为作为主要关注点的表始终是 FROM 子句中列出的第一个表。

常见陷阱

一个常见的错误是将外部表的筛选条件放在 WHERE 子句中，而不是 ON 子句中。这会有效地将 OUTER JOIN 变为 INNER JOIN。

错误示例： 此查询将不会返回 David，因为 WHERE e.hire_date > '2023-01-01' 在连接后筛选掉了他的 NULL 结果。

-- 错误地筛选掉了不匹配的行
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE e.hire_date > '2023-01-01'; -- WHERE 子句中对左表设置的条件

正确示例： 如果您想保留 NULL 结果，外部表的条件应作为 ON 子句的一部分。

-- 这种逻辑通常更复杂，取决于具体目标。
-- 对于筛选右表，这很简单：
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id AND d.name = 'Engineering';