MySQL 表连接（Inner Join/Left Join）ON 与 WHERE 条件的执行差异解析

MySQL 表连接的核心原理

MySQL 在处理多表连接时，默认主要依赖嵌套循环连接（Nested-Loop Join，以下简称 NLJ）算法（暂不考虑 Hash Join 或 Merge Join）。其核心逻辑是：首先确定一个驱动表（Driving Table），利用相关过滤条件对其执行单表查询；随后，遍历驱动表返回的每一行数据，将其关联字段作为过滤条件，到被驱动表（Driven Table）中查找匹配的记录。

第1步：理解 NLJ 执行伪代码

NLJ 的基本执行过程可简化为以下嵌套循环逻辑：

for each row in t1 {      // 遍历满足 t1 单表查询结果的每一条记录
    for each row in t2 {  // 对于某条 t1 记录，遍历 t2 中满足条件的记录
        if row satisfies join conditions,
            send to client; // 如果满足连接条件，则发送给客户端
    }
}

理解这一流程是分析 ON 与 WHERE 差异的基础。接下来，我们将基于具体的表结构进行验证。

准备工作：测试表结构与数据

为了演示连接条件的差异，我们创建两张关联表：student（学生表）和 course（课程表），并通过 stu_code 建立关联。

第2步：创建测试表结构

执行以下 SQL 语句创建测试所需的表：

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `stu_code` varchar(20) NOT NULL DEFAULT '',
  `stu_name` varchar(30) NOT NULL DEFAULT '',
  `stu_sex` varchar(10) NOT NULL DEFAULT '',
  `stu_age` int(10) NOT NULL DEFAULT '0',
  `stu_dept` varchar(30) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uq_stu_code` (`stu_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE `course` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `cou_code` varchar(20) NOT NULL DEFAULT '',
  `cou_name` varchar(50) NOT NULL DEFAULT '',
  `cou_score` int(10) NOT NULL DEFAULT '0',
  `stu_code` varchar(20) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_stu_code_cou_code` (`stu_code`,`cou_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

假设表中已录入若干学生和课程数据，其中包含不同成绩和学号分布。通过查看数据分布，我们可以更清晰地观察连接后的结果集变化。

Inner Join（内连接）：ON 与 WHERE 的等价性

在内连接中，无论将额外过滤条件写在 ON 还是 WHERE 后面，最终生成的执行计划和结果集通常是一致的。这是因为优化器会将内连接的 ON 条件统一转化为 WHERE 条件处理。

第3步：验证内连接中的条件位置

执行以下查询，同时使用 ON 条件筛选驱动表和被驱动表：

SELECT * FROM student
INNER JOIN course 
ON student.stu_code = course.stu_code
AND student.stu_code >= 3 
AND course.cou_score >= 80;

通过 EXPLAIN 命令查看执行计划：

进一步使用 SHOW WARNINGS 查看优化器优化后的实际执行 SQL：

分析结论：从 SHOW WARNINGS 的输出可以看出，原本写在 ON 后面的 student.stu_code >= 3 和 course.cou_score >= 80 被优化器移到了 WHERE 子句中。这证明在 Inner Join 中，ON 和 WHERE 对于过滤条件来说是等价的，优化器会进行扁平化处理。

Left Join（左连接）：条件位置的致命差异

外连接（如 Left Join）的设计初衷是保留驱动表的所有记录，即使在被驱动表中找不到匹配项。因此，ON 和 WHERE 的位置在此处会产生截然不同的结果。我们需要分情况讨论：

第4步：WHERE 作用于驱动表

当过滤条件位于 WHERE 子句且针对的是驱动表（student）时，它会在读取驱动表阶段直接生效，过滤掉不满足条件的行。

SQL 示例：

SELECT * FROM student
LEFT JOIN course 
ON student.stu_code = course.stu_code
WHERE student.stu_code >= 3;

执行计划与警告信息：

结果集表现：

分析：explain 显示 student 作为驱动表进行了全表扫描，但在获取数据后应用了 stu_code >= 3 的过滤。这意味着只有满足该条件的学生记录才会进入后续的“被驱动表查找”环节。这是符合直觉的过滤方式。

第5步：ON 作用于驱动表

这是一个常见的误区。如果在 ON 条件中包含针对驱动表的过滤（如 student.stu_code >= 3），该条件并不会过滤驱动表本身，而是作为连接匹配的条件。

SQL 示例：

SELECT * FROM student
LEFT JOIN course 
ON student.stu_code = course.stu_code 
AND student.stu_code >= 3;

执行计划与警告信息：

结果集表现：

分析：从结果集看，stu_code < 3 的学生依然存在，说明 ON 中的 stu_code >= 3 并未过滤驱动表。根据 NLJ 原理，这个条件是在尝试匹配被驱动表时才使用的。如果驱动表的某行数据不满足此条件，它在被驱动表中找不到匹配项，但由于是 Left Join，该行依然会被返回，只是被驱动表的字段全部填充为 NULL。因此，Outer Join 中不要将对驱动表的过滤放在 ON 里，否则会失去过滤效果。

第6步：ON 作用于被驱动表

这是正确配置外连接过滤的方式。将被驱动表的过滤条件放在 ON 中，可以确保即使匹配不到，驱动表的记录也会保留，而被驱动表字段填 Null。

SQL 示例：

SELECT * FROM student
LEFT JOIN course 
ON student.stu_code = course.stu_code 
AND course.cou_score >= 80;

执行计划与警告信息：

结果集表现：

分析：所有学生记录都被保留。对于成绩大于等于 80 的课程，正常显示；对于没有高分课程或没有课程的学生，course 相关字段显示为 NULL。这正是外连接期望的行为。

第7步：WHERE 作用于被驱动表

如果被驱动表的过滤条件错误地放在了 WHERE 子句中，会发生什么？

SQL 示例：

执行计划与警告信息：

结果集表现：

关键发现：观察 SHOW WARNINGS 的结果，会发现原本的 LEFT JOIN 被优化器改写成了 INNER JOIN！这是因为 WHERE 子句在连接完成之后执行，此时如果 cou_score 为 NULL（即未匹配成功），NULL >= 80 判断为假，该行直接被过滤掉。这导致左连接的特性（保留未匹配行）完全丧失。

总结

理解 MySQL 表连接的关键在于区分过滤发生的时机：

• Inner Join：ON 和 WHERE 语义等价，优化器会统一处理。
• Left Join：
  • 驱动表过滤：务必放在 WHERE 子句中。若放在 ON 中，仅影响匹配结果而不过滤驱动表原始行。
  • 被驱动表过滤：务必放在 ON 子句中。若放在 WHERE 中，会导致空值被过滤，进而使左连接退化为内连接。

外连接引入的核心价值在于“即使未被匹配，驱动表记录仍须保留”。遵循上述规则，才能确保查询逻辑与业务预期一致。

以上就是 MySQL 表连接（Inner Join/Left Join）中 ON 与 WHERE 条件差异的详细解析，更多关于 MySQL 性能优化的资料请关注本站其它相关文章！