一、前置核心认知

1. LEFT JOIN 的本质定义：以【左表】为主表/驱动表，左表的所有数据行都会被强制保留，无论右表是否有匹配的数据；
2. 右表匹配不到关联条件的数据行，所有字段值都会填充为 NULL；
3. 核心差异限定：仅在 LEFT/RIGHT/FULL JOIN 中，ON 和 WHERE 写过滤条件有区别；INNER JOIN 中两者效果完全一致，无任何区别。

二、核心区别一：执行时机 & 作用对象

✅ 情况1：过滤条件写在 LEFT JOIN ... ON 后面

• 执行时机：条件在「表与表执行关联匹配」的瞬间生效；
• 作用对象：仅对右表的数据进行过滤，不会对左表产生任何影响；
• 核心逻辑：先按ON后的条件筛选出右表符合要求的记录，再用筛选后的右表数据和左表做LEFT JOIN关联匹配；
• 关键特性：左表100%全量保留，哪怕右表无匹配数据，左表该行也正常显示，右表字段补NULL。

✅ 情况2：过滤条件写在 LEFT JOIN ... WHERE 后面

• 执行时机：条件在「两张表完成LEFT JOIN关联生成临时大表之后」生效；
• 作用对象：对LEFT JOIN关联后的最终临时结果集，做全局行过滤；
• 核心逻辑：先执行LEFT JOIN（左表全保留，右表无匹配则补NULL）生成完整临时表，再用WHERE后的条件对临时表逐行校验，不符合条件的行直接剔除；
• 关键特性：大概率丢失左表的数据行，这是LEFT JOIN场景的核心坑点。

三、核心区别二：对 NULL 值的处理

SQL 铁律，无任何例外：

任何判断条件与 NULL 做比较运算，结果永远是 FALSE

包括 NULL = 1、NULL != 0、NULL = '数学'、NULL > 60 等所有场景，判断结果均为 FALSE。
LEFT JOIN 中右表无匹配的数据行字段都是NULL，这个铁律是「两种写法结果不同」的根本原因。

四、实际案例+测试数据

准备测试表（极简业务场景）

-- 创建学生表（左表）
CREATE TABLE student (
  id INT PRIMARY KEY,
  name VARCHAR(20) NOT NULL
);

-- 创建成绩表（右表）
CREATE TABLE score (
  sid INT,
  subject VARCHAR(20) NOT NULL,
  score INT
);

-- 给学生表插入3条数据
INSERT INTO student (id, name) VALUES(1, '张三');
INSERT INTO student (id, name) VALUES(2, '李四');
INSERT INTO student (id, name) VALUES(3, '王五');
COMMIT;

-- 给成绩表插入3条数据
INSERT INTO score (sid, subject, score) VALUES (1, '数学', 90);
INSERT INTO score (sid, subject, score) VALUES (1, '语文', 85);
INSERT INTO score (sid, subject, score) VALUES (2, '数学', 59);
COMMIT;

SELECT * FROM student;
SELECT * FROM score;

左表 student（学生表，主表）

id	name
1	张三
2	李四
3	王五

右表 score（成绩表，副表）

sid	subject	score
1	数学	90
1	语文	85
2	数学	59

案例1：条件写在 ON 后 → LEFT JOIN ON 过滤

需求：查询所有学生的数学成绩（要求保留全部学生，无成绩则显示空）

SELECT s.id, s.name, sc.score
FROM student s
LEFT JOIN score sc ON s.id = sc.sid AND sc.subject = '数学';

✅ 查询结果

id	name	score
1	张三	90
2	李四	59
3	王五	NULL

✅ 结果解读

左表student的3条数据全部保留，完全符合LEFT JOIN的设计初衷；王五无数学成绩，右表字段补NULL，这是业务中「查全量主表+关联子表数据」的正确写法。

案例2：条件写在 WHERE 后 → LEFT JOIN … WHERE 过滤

需求：同样查询学生的数学成绩，将过滤条件写在WHERE后

SELECT s.id, s.name, sc.score
FROM student s
LEFT JOIN score sc ON s.id = sc.sid 
WHERE sc.subject = '数学';

✅ 查询结果

id	name	score
1	张三	90
2	李四	59

✅ 结果解读

左表「王五」的记录直接被过滤消失。
原因：先执行LEFT JOIN生成和案例1一致的临时表，再执行WHERE sc.subject='数学'，王五对应的sc.subject是NULL，NULL='数学'的判断结果为FALSE，该行被剔除。

案例3：高频易错写法（隐性转为内连接，失去LEFT JOIN意义）

SELECT * FROM A LEFT JOIN B ON A.id=B.id WHERE B.xx = xxx;

此写法的最终效果 完全等价于 INNER JOIN，是新手90%会踩的坑！
原因：WHERE过滤右表字段时，会把所有右表为NULL的行全部筛掉，左表的「全量保留」特性彻底失效。

五、ON 后支持多条件吗？

✅ 完全支持！ON 后面可以通过 AND 拼接任意多个过滤条件，所有条件均遵循「先过滤右表，再做关联」的逻辑，不会影响左表数据。

示例：查询所有学生数学≥60分的成绩

SELECT s.id, s.name, sc.score
FROM student s
LEFT JOIN score sc ON s.id = sc.sid 
  AND sc.subject = '数学' 
  AND sc.score >= 60;

✅ 查询结果

id	name	score
1	张三	90
2	李四	NULL
3	王五	NULL

六、终极准则：什么时候写ON？什么时候写WHERE？（必记）

✅ 优先写在 ON 后的场景（90%的业务需求）

所有对右表的过滤条件，全部写在ON后面

适用需求：需要保留左表全部数据，同时只匹配右表符合条件的记录 → 这是LEFT JOIN的核心业务场景。
典型场景：查「全部用户」的「本月订单」、查「全部商品」的「热销评价」、查「全部员工」的「绩效数据」。

✅ 适合写在 WHERE 后的场景（10%的特殊需求）

只对左表的字段做全局过滤，写在WHERE后面

适用需求：对主表（左表）本身的数据做筛选，再关联右表。这种写法安全无坑，不会丢失左表数据。

示例：查询所有姓张的学生的数学成绩

SELECT s.id, s.name, sc.score
FROM student s
LEFT JOIN score sc ON s.id = sc.sid AND sc.subject = '数学'
WHERE s.name LIKE '张%'; -- 过滤左表字段，安全合规

七、核心口诀

1. LEFT JOIN 中，想保左表，条件写ON；想筛主表，条件写WHERE；
2. 右表过滤写ON，左表过滤写WHERE；
3. ON 管「关联匹配+右表过滤」，WHERE 管「全局结果筛选」；
4. INNER JOIN 无区别，LEFT JOIN 天差地别。

---

总结核心差异

写法	执行时机	作用对象	左表数据是否保留	核心特点
LEFT JOIN ON 条件	表关联时生效	仅过滤右表	100%全保留	符合LEFT JOIN设计初衷，推荐主流使用
LEFT JOIN WHERE 条件	关联完成后生效	过滤最终临时表	大概率丢失	易隐性转为内连接，谨慎使用