ON 子句和 WHERE 子句对 LEFT JOIN 结果的影响有什么不同？

考察要点

这道题是 SQL 面试中的高频基础题，面试官想通过这个问题考察你是否真正理解 SQL 的逻辑执行顺序，以及 JOIN 操作的底层原理。它重点测试你对以下两点的掌握程度：

1. Join Condition (ON) vs. Filter Condition (WHERE)：你是否清楚两者在 SQL 查询处理流程中的作用阶段不同。
2. OUTER JOIN (LEFT/RIGHT JOIN) 的核心语义：你是否理解 LEFT JOIN “保留左表所有行”这一特性是如何与 ON 和 WHERE 子句相互作用的。

回答这个问题的关键在于，清晰地阐述 ON 子句在生成中间表阶段生效，而 WHERE 子句在中间表生成后进行过滤，并解释这种时序差异对 LEFT JOIN 结果集的决定性影响。

核心概念

ON 和 WHERE 的根本区别在于它们在 SQL 查询逻辑处理管道中的位置不同。一个标准的 SQL 查询逻辑执行顺序如下： FROM -> ON -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT -> DISTINCT -> ORDER BY -> LIMIT

1. ON 子句：

   • 作用阶段：在 JOIN 操作期间，用于定义两个表之间行匹配的规则。
   • 核心功能：ON 子句是 “生成” 过程的一部分。它决定了右表中的哪些行可以与左表中的行“配对”成功，形成一个临时的中间结果集。
   • 对于 LEFT JOIN：
     • 如果左表的某一行，根据 ON 子句的条件，在右表中找不到任何匹配的行，那么左表的这一行仍然会被包含在中间结果集中。
     • 右表对应的所有列在这种情况下都会被填充为 NULL。
     • 换言之，ON 子句中的条件，即使是对右表的过滤，也只是决定了右表“有没有资格参与匹配”，而不会影响左表行的保留。

2. WHERE 子句：

   • 作用阶段：在 FROM、ON、JOIN 操作完成之后，对已经生成的临时结果集进行过滤。
   • 核心功能：WHERE 子句是 “过滤” 过程的一部分。它像一个筛子，逐行检查临时结果集，只有满足 WHERE 条件的行才会被保留下来，进入后续的处理阶段（如 GROUP BY）。
   • 对于 LEFT JOIN：
     • 当 WHERE 子句中包含对右表列的过滤条件时（例如 WHERE right_table.column = 'value'），它会在 LEFT JOIN 已经完成后执行。
     • 对于那些在 JOIN 阶段因为没有匹配而导致右表列为 NULL 的行，WHERE 子句的条件（如 right_table.column = 'value'）通常会判断为 FALSE 或 UNKNOWN，从而将这些行过滤掉。
     • 这就导致了一个非常重要的副作用：对右表列使用 WHERE 子句进行过滤，通常会使 LEFT JOIN 的效果退化为 INNER JOIN。

数据示例

假设我们有两张表：students (学生表) 和 scores (成绩表)。

students 表

scores 表

注意：学生 Carol (id=3) 没有任何成绩记录。

场景 1：条件在 ON 子句中

我们想查询所有学生以及他们的数学成绩，没有数学成绩的学生也要列出。

 1SELECT
 2    s.name,
 3    sc.subject,
 4    sc.score
 5FROM
 6    students s
 7LEFT JOIN
 8    scores sc ON s.id = sc.student_id AND sc.subject = 'Math';

执行过程：

1. FROM students s LEFT JOIN scores sc: 准备连接两张表。
2. ON s.id = sc.student_id AND sc.subject = 'Math': 定义匹配规则。
   • 对 Alice (id=1)，在 scores 表中找到 student_id=1 且 subject='Math' 的行，匹配成功。
   • 对 Bob (id=2)，在 scores 表中找到 student_id=2 且 subject='Math' 的行，匹配成功。
   • 对 Carol (id=3)，在 scores 表中找不到任何 student_id=3 的行，匹配失败。
3. LEFT JOIN 的语义保证：即使 Carol 匹配失败，也要保留 Carol 这一行，其 scores 表对应的列填充为 NULL。

最终结果：

这个结果符合预期：列出了所有学生，并附上他们的数学成绩（如果有的话）。

场景 2：条件在 WHERE 子句中

我们尝试用 WHERE 子句来达到同样的目的。

 1SELECT
 2    s.name,
 3    sc.subject,
 4    sc.score
 5FROM
 6    students s
 7LEFT JOIN
 8    scores sc ON s.id = sc.student_id
 9WHERE
10    sc.subject = 'Math';

执行过程：

1. FROM students s LEFT JOIN scores sc ON s.id = sc.student_id: 首先，根据 student_id 进行连接。
   • Alice 匹配到两条成绩。
   • Bob 匹配到一条成绩。
   • Carol 没有匹配到成绩。
2. 生成临时结果集（WHERE 执行前）： | name | subject | score | |-------|---------|-------| | Alice | Math | 90 | | Alice | English | 85 | | Bob | Math | 95 | | Carol | NULL | NULL |
3. WHERE sc.subject = 'Math': 对上述临时结果集进行过滤。
   • 第一行 sc.subject = 'Math'，保留。
   • 第二行 sc.subject = 'English'，不等于 'Math'，丢弃。
   • 第三行 sc.subject = 'Math'，保留。
   • 第四行 sc.subject 是 NULL，NULL = 'Math' 的判断结果是 UNKNOWN，不满足 WHERE 条件，丢行。

最终结果：

Carol 的记录被过滤掉了！LEFT JOIN 退化成了 INNER JOIN 的效果。

常见低分回答

1. 混淆概念：“ON 和 WHERE 都是用来过滤数据的，ON 是连接时过滤，WHERE 是连接后过滤，效果差不多。” —— 这是最典型的错误，完全没有理解 LEFT JOIN 的保留左表特性，忽略了 WHERE 会过滤掉 NULL 行导致语义变化。
2. 只知其一，不知其二：“LEFT JOIN 时，条件必须写在 ON 里。” —— 虽然结论在很多场景下是正确的，但没有解释为什么。面试官想听的是 "Why"，而不是 "What"。你需要解释 WHERE 如何导致 LEFT JOIN 退化为 INNER JOIN。
3. 对 INNER JOIN 的错误类比：“INNER JOIN 里 ON 和 WHERE 可以互换，所以 LEFT JOIN 也差不多。” —— 这是一个危险的类比。对于 INNER JOIN，由于没有 NULL 补充行的问题，优化器确实有更大的自由度来转换 ON 和 WHERE 的条件，结果集通常是等价的。但这个结论绝对不能推广到 OUTER JOIN。
4. 忽略 NULL 的处理：“WHERE sc.subject = 'Math' 会把 Carol 那行过滤掉，因为她的 subject 不是 'Math'。” —— 这种说法不精确。Carol 的 subject 是 NULL，NULL 与任何值的比较（包括 NULL 本身，使用 =）结果都是 UNKNOWN，WHERE 子句只保留结果为 TRUE 的行。精确描述 NULL 的行为是加分项。

工程实践与优化

1. 表达清晰的业务意图：

   • 如果你的意图是“在连接时就排除掉不符合条件的右表记录，但依然保留所有左表记录”，请务必将条件写在 ON 子句中。这是 LEFT JOIN 的标准用法。
   • 如果你的意图是“先完成连接，然后对产生的结果集进行整体过滤”，才应该把条件写在 WHERE 子句中。要特别小心这种用法，确保你真的想把 LEFT JOIN 当 INNER JOIN 用。

2. 避免隐式 INNER JOIN 转换：在生产代码中，最常见的 LEFT JOIN bug 就是在 WHERE 子句中无意中加入了对右表的过滤条件，导致本应保留的左表数据丢失。Code Review 时应特别关注 LEFT JOIN + WHERE 的组合。

3. 利用 EXPLAIN 分析执行计划：

   • 当你不确定 SQL 如何执行时，使用 EXPLAIN 是最好的方法。
   • 对于 ON 子句的场景，EXPLAIN 结果通常会显示 LEFT JOIN。
   • 对于 WHERE 子句导致退化的场景，MySQL 8.0 的 EXPLAIN 在 Extra 信息中可能会提示 Using where，并且你可以观察到 rows 估算值的显著减少，其行为更像 INNER JOIN。虽然 type 可能仍然显示为 LEFT JOIN，但结果集的行数已经证明了其 INNER JOIN 的行为。

4. 对 NULL 的特殊处理：如果你想在 WHERE 子句中筛选出那些在 LEFT JOIN 中未匹配成功的行，正确的写法是 WHERE right_table.column IS NULL。这是一种常见且正确的用法，例如查找所有有学生信息但没有成绩的学生。

常见追问与延伸题目

1. 追问：那在 INNER JOIN 中，把条件放在 ON 和 WHERE 有区别吗？

   • 回答要点：
     • 从结果集上说：对于 INNER JOIN，绝大多数情况下，将过滤条件放在 ON 或 WHERE 中，最终返回的结果集是等价的。因为 INNER JOIN 只保留两边都匹配的行，任何一方不满足条件都会导致整行被排除。
     • 从性能和执行计划上说：理论上，ON 条件可能让数据库在更早的阶段（连接操作时）就过滤掉数据，减少中间结果集的大小。但现代的 SQL 优化器非常智能，通常能够理解条件的等价性，并自动重写查询以生成最优的执行计划。因此，在 INNER JOIN 中，性能差异通常可以忽略不计。
     • 从代码规范和可读性上说：最佳实践是，用于定义表之间关联关系的条件放在 ON 子句中，而用于过滤结果集的条件放在 WHERE 子句中。这使得 SQL 的逻辑更清晰，更易于他人理解和维护。

2. 追问：如果我就是想在 LEFT JOIN 之后，过滤出右表某个字段不为特定值的行，同时保留左表那些未匹配的行（右表为NULL），该怎么写？

   • 回答要点：这是一个很好的问题，考察了对 NULL 逻辑的综合运用。正确的写法是在 WHERE 子句中把 NULL 的情况也考虑进去。
   • SQL 示例：假设要过滤掉数学成绩，但保留其他科目成绩以及没有成绩的学生。
     sql

     ⧉⛶

      1SELECT s.name, sc.subject, sc.score
      2FROM students s
      3LEFT JOIN scores sc ON s.id = sc.student_id
      4WHERE sc.subject <> 'Math' OR sc.subject IS NULL;

   • 解释：WHERE 子句的条件 sc.subject <> 'Math' OR sc.subject IS NULL 能够正确处理：
     • sc.subject <> 'Math'：保留所有非数学科目的成绩行。
     • sc.subject IS NULL：保留所有在 LEFT JOIN 中未匹配成功的行（例如 Carol），因为这些行的 sc.subject 为 NULL。

3. 追问：RIGHT JOIN 和 FULL OUTER JOIN 的情况是怎样的？

   • 回答要点：原理完全相同，只是作用的表不同。
     • RIGHT JOIN：ON 和 WHERE 的区别与 LEFT JOIN 类似，只不过是“保留右表所有行”。WHERE 子句中对左表的过滤条件，会导致 RIGHT JOIN 退化为 INNER JOIN。
     • FULL OUTER JOIN (MySQL 8.0+ 支持)：它会保留左右两边所有表的数据。WHERE 子句中对任何一方（左表或右表）的过滤条件（非 IS NULL），都会破坏 FULL OUTER JOIN 的语义，使其部分或完全退化。例如，WHERE left_table.col = 'val' 会过滤掉所有右表有而左表没有的行。正确的做法依然是将这类“匹配时”的过滤条件放在 ON 子句中。