SQL LEFT JOIN：保留左表的所有行

Q: INNER JOIN 和 LEFT JOIN 有什么区别？

INNER JOIN 只返回在两张表中都有匹配的行。LEFT JOIN 还会返回左表中没有匹配的行，并用 NULL 填充右表的列。

Q: LEFT JOIN 和 LEFT OUTER JOIN 一样吗？

一样。OUTER 可以省略，两种写法在所有数据库中返回相同的结果。

Q: 为什么我的 LEFT JOIN 表现得像 INNER JOIN？

很可能是 WHERE 子句中有针对右表某列的条件。对于没有匹配的行，该列为 NULL，条件不成立，这些行就被删除了。请把该条件移到 ON 子句中。

第 5.3 课 · 阅读时间：约 7 分钟 · 练习题 ↓

SQL LEFT JOIN 返回左表的每一行以及右表中匹配的行；没有匹配时，右表的列为 NULL。在本课中，你将学习 LEFT JOIN 的语法、如何查找没有匹配的行，以及一个会悄悄把 LEFT JOIN 变成 INNER JOIN 的 WHERE 错误。

INNER JOIN 会丢弃没有匹配的行。但很多时候，你需要的恰恰是这些行：没有库存的电影、本月没有下单的客户、从未卖出的商品。LEFT JOIN 会保留它们。

SQL LEFT JOIN：保留左表的所有行，附加右表中匹配的行，没有匹配的行在右表的列中得到 NULL

LEFT JOIN 返回什么

左表（FROM 之后的表）的每一行至少在结果中出现一次。
如果某行在右表中有匹配，它会与每个匹配行组合，就像 INNER JOIN 一样。
如果某行没有匹配，它只出现一次，右表的所有列都为 NULL。

LEFT JOIN 和 LEFT OUTER JOIN 是一样的；OUTER 一词可以省略。

基本语法

SELECT left_table.column1, right_table.column2
FROM left_table
LEFT JOIN right_table ON left_table.key_column = right_table.key_column;

电影及其拷贝

Sakila 中有 1000 部电影，但并非都有库存。使用 LEFT JOIN 时，没有拷贝的电影会保留在结果中：

SELECT f.film_id, f.title, i.inventory_id, i.store_id
FROM film AS f
LEFT JOIN inventory AS i ON i.film_id = f.film_id
ORDER BY i.inventory_id IS NULL DESC, f.title
LIMIT 10;

结果：没有拷贝的电影排在最前面；它们的 inventory_id 和 store_id 为 NULL。

如果用 INNER JOIN，同样的查询会丢失这 42 部电影。

查找没有匹配的行

要只查找没有匹配的行，请添加一个 WHERE 条件，检查右表的某一列是否为 NULL。应检查右表的键列：只有在没有匹配行时它才为 NULL。

SELECT f.film_id, f.title
FROM film AS f
LEFT JOIN inventory AS i ON i.film_id = f.film_id
WHERE i.inventory_id IS NULL
ORDER BY f.title;

结果：在任何门店都没有拷贝的 42 部电影。

这种写法称为反连接（anti-join），用于回答“缺少什么”这类问题。再举一个例子，从未被租出过的拷贝：

SELECT i.inventory_id, f.title, i.store_id
FROM inventory AS i
INNER JOIN film AS f ON f.film_id = i.film_id
LEFT JOIN rental AS r ON r.inventory_id = i.inventory_id
WHERE r.rental_id IS NULL;

结果：一个从未被租出过的拷贝。

WHERE 陷阱

把对右表的过滤条件写在 WHERE 中，会删除该表列为 NULL 的行，于是 LEFT JOIN 的行为就和 INNER JOIN 一样了。比较下面两个查询：两者都应该列出所有客户，以及他们在 2005 年 8 月 23 日及之后的付款。

-- 错误：没有这类付款的客户消失了
SELECT c.customer_id, c.last_name, p.amount, p.payment_date
FROM customer AS c
LEFT JOIN payment AS p ON p.customer_id = c.customer_id
WHERE p.payment_date >= '2005-08-23'
ORDER BY c.customer_id;

-- 正确：条件是连接的一部分
SELECT c.customer_id, c.last_name, p.amount, p.payment_date
FROM customer AS c
LEFT JOIN payment AS p ON p.customer_id = c.customer_id
                      AND p.payment_date >= '2005-08-23'
ORDER BY c.customer_id;

结果：第一个查询只返回在该期间付过款的客户。第二个查询保留了全部 599 位客户；8 月底没有付款的客户，其 amount 和 payment_date 为 NULL。

规则是：用于限制匹配的右表条件写在 ON 中。用于从结果中删除行的左表条件写在 WHERE 中。唯一应该写在 WHERE 中的右表条件，是用于反连接的 IS NULL。

表的顺序很重要

在 INNER JOIN 中，交换表的位置不会改变结果。在 LEFT JOIN 中则会：FROM 之后的表是保留所有行的那张表。film LEFT JOIN inventory 保留所有电影；inventory LEFT JOIN film 保留所有拷贝，而每个拷贝都有对应的电影，所以没有拷贝的电影会丢失。

要点：

LEFT JOIN 保留左表的每一行；没有匹配的行在右表的列中得到 NULL。
LEFT JOIN 和 LEFT OUTER JOIN 是一样的。
LEFT JOIN ... WHERE right_key IS NULL 用于查找没有匹配的行（反连接）。
在 WHERE 中对右表进行过滤会把 LEFT JOIN 变成 INNER JOIN；请把条件放到 ON 中。
FROM 之后的表是保留所有行的那张表。

常见问题

INNER JOIN 和 LEFT JOIN 有什么区别？

INNER JOIN 只返回在两张表中都有匹配的行。LEFT JOIN 还会返回左表中没有匹配的行，并用 NULL 填充右表的列。

LEFT JOIN 和 LEFT OUTER JOIN 一样吗？

一样。OUTER 可以省略，两种写法在所有数据库中返回相同的结果。

为什么我的 LEFT JOIN 表现得像 INNER JOIN？

很可能是 WHERE 子句中有针对右表某列的条件。对于没有匹配的行，该列为 NULL，条件不成立，这些行就被删除了。请把该条件移到 ON 子句中。