VillageSQL is a drop-in replacement for MySQL with extensions.
All examples on this page work on VillageSQL. Install Now →
LEFT JOIN 保留左表的每一行,无论它是否匹配,这就是您回答另一个问题的方式:什么没有匹配。
开始之前,请用 mysql -u root -p sakila 打开客户端。
LEFT JOIN 的工作原理
COLUMN_LIST 替换为您想要的列,LEFT_TABLE 替换为您想保留其每一行的表,RIGHT_TABLE 替换为您从中取出额外列的表,每个 COLUMN_NAME 替换为该侧用来关联两者的列。
对于匹配的行,服务器的行为与内连接相同。对于没有匹配到任何东西的左表行,它仍然保留该行,并把右表的每一列填为 NULL。LEFT OUTER JOIN 是同一个子句的完整写法,OUTER 这个词不添加任何东西。
示例
- 保留未匹配的行
Sakila 有 6 种语言,而每部影片都是英语,所以有 5 种语言没有匹配到任何影片。内连接会丢弃它们。这个连接不会。
- 只列出没有匹配到任何东西的行
对右表中一个在真实匹配中永远不会是 NULL 的列加上 WHERE,您就恰好保留了未匹配的行。
f.film_id 是 film 的主键,所以匹配的行总是有它。那里的 NULL 只可能来自连接为没有匹配到任何东西的行所做的填充。测试右表的任何 NOT NULL 列,同样的推理都成立。主键是最安全的选择,因为它在每个表上都是 NOT NULL。
- 它在实践中回答的问题
并不是目录中的每部影片都在货架上。inventory 保存着拷贝,所以没有 inventory 行的影片就是没有人能租到的影片。
LEFT JOIN 加上 WHERE <right key> IS NULL,就是您在一个表中找出在另一个表中没有任何匹配的行的方式。
WHERE 中的筛选会抵消外连接
这是使用LEFT JOIN 时让人花费最多时间的错误。给右表加一个普通条件看起来无害。
f.title 为 NULL 的行,然后 WHERE 测试每一行,而 NULL LIKE 'ACADEMY%' 是 NULL 而不是真,所以 WHERE 丢弃了它们。
把同一个条件移进 ON,它就成为匹配判断的一部分。
LEFT JOIN 中,针对右表的条件放在 ON,针对左表的条件放在 WHERE。唯一有意为之的例外是示例 2 中的 IS NULL,在那里丢弃已匹配的行正是目的所在。
小结
- 用
LEFT JOIN保留左表的每一行,无论是否匹配。 - 没有匹配到任何东西的行,其右侧每一列都会是 NULL。
- 用
IS NULL测试右表的一个NOT NULL列,只列出未匹配的行。 - 针对右表的条件放在
ON,因为放在WHERE中会丢弃未匹配的行。 - 把
LEFT OUTER JOIN理解为同一个子句的另一种写法。
另请参阅
- INNER JOIN — 只保留匹配的连接
- RIGHT JOIN — 从另一侧看的同一个想法
- IS NULL — 让未匹配的行显现出来的测试
- EXISTS — 不用连接提出的同一个问题
- MySQL JOIN 性能与常见错误 — 同一个陷阱,以错误报告的形式呈现