外连接中的 WHERE 筛选陷阱
了解为什么在 WHERE 中筛选外连接列会悄然将其变成内连接
外连接中的 WHERE 筛选陷阱 是 CoddyKit 上的免费 Coding Interview Prep 课时。 这是第 4 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 Coding Interview Prep 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 Coding Interview Prep 课程共包含 4 节课。
人人都会踩的陷阱
这是面试官设置的最常见外连接错误：“显示每个客户及其 2024 年的订单，包括没有 2024 年订单的客户。”
候选人写出 LEFT JOIN，然后在 WHERE 中添加日期筛选，没有 2024 年订单的客户就会悄无声息地消失。LEFT JOIN 悄然退化为 INNER JOIN。理解其中原因，是高级水平的体现。
有问题的查询
下面就是这个错误。它看起来很合理：保留所有客户，连接他们的订单，再筛选 2024 年的订单。
但是没有订单，或者没有 2024 年订单的客户，会从结果中消失。必须包含这些客户的要求没有得到满足。
-- BUG: drops customers with no 2024 order
SELECT c.name, o.id, o.order_date
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.order_date >= '2024-01-01';为什么会出错
请回忆操作顺序：JOIN 会先运行，为未匹配的客户生成行，并在每个订单列中填入 NULL。然后才运行 WHERE。
对于未匹配的客户，o.order_date 为 NULL，因此 o.order_date >= '2024-01-01' 的结果是 UNKNOWN，而不是真值。WHERE 只保留结果为 TRUE 的行，因此 NULL 行会被筛掉，恰好就是 LEFT JOIN 努力保留的那些行。
NULL 会使筛选失效
与 NULL 进行任何比较都会得到 UNKNOWN：NULL >= '2024-01-01' 是 UNKNOWN，NULL = 5 是 UNKNOWN，甚至 NULL <> 5 也是 UNKNOWN。
由于 WHERE 只通过结果为 TRUE 的行，所有保留下来的无匹配行都会被丢弃。在右表列上加一个 WHERE 谓词，就能彻底破坏外连接的全部目的。
修复：在 ON 中筛选
将筛选条件移入 ON 子句。在那里，它会成为匹配条件，并在行被保留之前应用，因此未匹配的客户仍会带着 NULL 保留下来。
-- CORRECT: filter lives in ON
SELECT c.name, o.id, o.order_date
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
AND o.order_date >= '2024-01-01';
-- customers with no 2024 order: kept, NULL orderON 与 WHERE 一句话说明
面试时可以这样说明这条规则：
对于保留的（外连接）表，针对另一张表的条件应放在 ON 中；针对保留表本身的条件应放在 WHERE 中。
ON决定什么算作匹配（在连接过程中运行）。WHERE筛选最终行（在连接之后运行，并移除 NULL 行）。
结果对比
相同的数据，放置位置不同，答案也不同。假设 Carol 没有 2024 年的订单。
- 在 WHERE 中筛选：Carol 消失了，实质上变成了 INNER JOIN。
- 在 ON 中筛选：Carol 会带着 NULL 订单列出现一次，满足需求。
输出结果的差异正是这个陷阱的关键。
-- ON version output
-- Alice | 50 | 2024-03-01
-- Bob | 20 | 2024-05-02
-- Carol | NULL | NULL <-- preservedWHERE 何时确实正确
外连接中的 WHERE 并不总是错误。筛选保留表是可以的，因为它不会涉及连接产生的 NULL。
上一课中的反连接还有意使用 WHERE o.id IS NULL 来利用这一行为。关键在于判断您面对的是哪一种情况。
-- Fine: filtering the preserved (left) table
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.country = 'US';识别陷阱的方法
检查外连接时，请查看 WHERE 子句中针对非保留表的谓词（IS NULL 反连接检查除外）。
如果您在 WHERE 中看到 o.someColumn = ...，或者看到针对外连接一侧的范围或相等性检查，就要警惕这个陷阱。请问自己：“这会不会把我的 LEFT JOIN 变成 INNER JOIN？”通常确实会。
多个条件
您可以同时采用这两种放置方式。右表的匹配条件放在 ON 中；真正的连接后筛选条件，如果针对左表，就放在 WHERE 中。两者可以清晰地共存。
SELECT c.name, o.id, o.amount
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.id
AND o.amount > 100 -- match condition
WHERE c.signup_year = 2023; -- preserved-table filter如何口头解释
面试时，请说明其中的机制，而不只是说出修复方法：
“连接会先运行，并将未匹配的右表列填入 NULL。针对这些列的 WHERE 谓词在 NULL 行上会得到 UNKNOWN，而 WHERE 会丢弃非 TRUE 的行，因此外连接会收缩为 INNER JOIN。将谓词放在 ON 中，就能让它继续作为匹配条件，同时保留未匹配的行。”这个解释通常很有说服力。
快速检查
您必须列出所有客户及其 2024 年的订单，同时保留没有 2024 年订单的客户。
回顾
在 WHERE 中筛选非保留表的列，会悄无声息地将外连接变成 INNER JOIN，因为未匹配行产生的 NULL 会使谓词结果为 UNKNOWN，而 WHERE 会丢弃这些行。
- 针对外连接表的匹配条件放在
ON中。 - 针对保留表的筛选条件放在
WHERE中。 - WHERE 中的
IS NULL是有意使用的反连接，并不是这个陷阱。 - 说明操作顺序，以证明您真正理解了它。
常见问题解答
「外连接中的 WHERE 筛选陷阱」课时是免费的吗？
是的 — 「外连接中的 WHERE 筛选陷阱」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 Coding Interview Prep 课程的其余内容，请升级到 CoddyKit PRO。 Coding Interview Prep 课程共包含 4 节课。
「外连接中的 WHERE 筛选陷阱」这节课中我会学到什么？
了解为什么在 WHERE 中筛选外连接列会悄然将其变成内连接 你通过在浏览器中直接运行的动手代码来练习 Coding Interview Prep，全天候 AI 导师会在你学习这节课的过程中回答你的问题。
学习 Coding Interview Prep 需要有经验吗？
无需任何先前经验。CoddyKit 上的 Coding Interview Prep 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 4 节课，共 4 节。
「外连接中的 WHERE 筛选陷阱」课时需要多长时间？
大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。
我能在这节 Coding Interview Prep 课中编写并运行代码吗？
能。每节 Coding Interview Prep 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。
此课程中的所有课时
- LEFT JOIN 与保留未匹配行
- RIGHT 和 FULL OUTER JOIN 语义
- 查找没有匹配项的行（反连接）
- 外连接中的 WHERE 筛选陷阱