JOIN 后面使用on过滤和WHERE 条件过滤的区别

        在 SQL 中，LEFT JOIN 和 WHERE 条件的使用方式会直接影响查询结果。以下是两种情况的区别及其影响的详细说明：

---

1. LEFT JOIN 后面加条件

        LEFT JOIN 是指左表中的所有记录都会出现在结果集中，即使右表中没有匹配的记录。在 LEFT JOIN 的 ON 子句中加条件，会限制连接的匹配行为，但不会过滤掉左表的记录。

示例：

SELECT *
FROM A
LEFT JOIN B ON A.id = B.a_id AND B.status = 'active';

• 解释：

  • 这段查询会返回表 A 中的所有记录。
  • 如果表 B 中的记录满足 A.id = B.a_id 且 B.status = 'active'，则将匹配的记录加入结果集。
  • 如果表 B 中没有匹配的记录，或者匹配的记录不满足 B.status = 'active' 条件，则结果集中对应 B 的列将是 NULL。

• 影响：

  • ON 子句中的条件只影响右表的匹配行为，不会剔除左表中的记录。
  • 即使条件不成立，左表的记录也会保留在结果中，只不过右表的列值会是 NULL。

---

2. WHERE 后面加条件

        在 LEFT JOIN 查询中，如果在 WHERE 子句中加条件，这个条件会作用于整个结果集。由于 LEFT JOIN 会产生 NULL 值，如果 WHERE 子句中包含条件且这些条件不允许 NULL 值存在，某些左表的记录可能会被过滤掉。

示例：

SELECT *
FROM A
LEFT JOIN B ON A.id = B.a_id
WHERE B.status = 'active';

• 解释：

  • 这段查询会先进行 LEFT JOIN，即返回表 A 中的所有记录，同时尝试从表 B 中匹配记录。
  • 但是，WHERE B.status = 'active' 会过滤掉 B.status 不等于 'active' 的记录（包括 B 为 NULL 的情况）。
  • 这导致一些左表的记录可能会被过滤掉，从而改变了 LEFT JOIN 的语义。

• 影响：

  • WHERE 子句过滤整个结果集，最终结果中只保留那些符合 WHERE 条件的记录。
  • 如果 B 中没有匹配的记录（即 B 的列为 NULL），这些记录也会被过滤掉。

---

3. 两者的核心区别

• ON 条件（LEFT JOIN 后加条件）：

  • 只影响表的连接方式和右表的匹配行为，不会过滤左表的记录。
  • 左表的记录始终保留，只是右表的列值可能为 NULL。

• WHERE 条件：

  • 作用于整个结果集，可能过滤掉部分左表的记录。
  • 如果 WHERE 子句中包含右表的条件，那么不匹配的右表记录会导致整行被过滤。

---

4. 对比示例

表结构和数据：

表 A：

id	name
1	Alice
2	Bob
3	Carol

表 B：

a_id	status
1	active
2	inactive

示例 1：ON 条件

SELECT *
FROM A
LEFT JOIN B ON A.id = B.a_id AND B.status = 'active';

结果：

id	name	a_id	status
1	Alice	1	active
2	Bob	NULL	NULL
3	Carol	NULL	NULL

• 表 A 的所有记录都保留。
• 只有表 B 中 status = 'active' 的记录会被连接，其他的不匹配时返回 NULL。

---

示例 2：WHERE 条件

SELECT *
FROM A
LEFT JOIN B ON A.id = B.a_id
WHERE B.status = 'active';

结果：

id	name	a_id	status
1	Alice	1	active

• 由于 WHERE B.status = 'active' 会过滤掉 B.status 为 NULL 的记录，表 A 中的记录 2 和 3 被过滤。

---

5. 总结

• ON 条件： 保留左表所有记录，条件仅影响右表的匹配。
• WHERE 条件： 作用于整个结果集，可能导致部分左表记录被过滤。
• 实际使用建议：
  • 如果要保留左表的所有记录并仅限制右表的匹配行为，使用 ON 条件。
  • 如果需要对结果集进行进一步过滤，则使用 WHERE 条件，但要注意它会影响到 LEFT JOIN 的语义。