面试官：left join后用on还是where？区别真的很大！

前言

“写 SQL 查询时，你是否也曾纠结过，条件到底该放在 JOIN 上还是 WHERE 上？每次写到这里，脑海中都会浮现出这两个选项的优劣对比，就像是在选择两个看似相似却又略有不同的武器，打击目标的效率可能会因此大相径庭。那么，这两者到底有何不同？今天，我们就来一探究竟。”

甩出问题

使用连接

select bm.*,ahv.TEXT_ FROM budget_management bm
LEFT JOIN act_hi_procinst ahp ON  ahp.BUSINESS_KEY_= bm.id
LEFT JOIN act_hi_varinst_copy1 ahv on ahv.PROC_INST_ID_ = ahp.PROC_INST_ID_ and ahv.NAME_ = 'approvalResult' and ahv.TASK_ID_ is NULL

使用where条件

select bm.*,ahv.TEXT_ FROM budget_management bm
LEFT JOIN act_hi_procinst ahp ON  ahp.BUSINESS_KEY_= bm.id
LEFT JOIN act_hi_varinst_copy1 ahv on ahv.PROC_INST_ID_ = ahp.PROC_INST_ID_ and ahv.NAME_ = 'approvalResult' where TASK_ID_ is NULL

sql的执行顺序

1.	**FROM 子句**：
- 首先确定主表，通常是 FROM 子句中的第一个表。
- 如果存在连接（JOIN），则根据 JOIN 子句指定的条件将多个表进行连接。连接的方式可以是内连接（INNER JOIN）、左连接（LEFT JOIN）、右连接（RIGHT JOIN）、全连接（FULL JOIN）等。

2.	**ON 子句**：
 - 在 JOIN 操作中，ON 子句用于指定连接条件。连接时，数据库会使用这些条件来匹配表中的记录。
 - 这个步骤在多表连接时非常关键，因为它决定了哪些记录会被连接在一起。

3.	**WHERE 子句**：
 - 在所有表连接完成后，WHERE 子句用于过滤连接后的结果集。
 - 这一阶段会筛选出符合 WHERE 条件的记录。

4.	**GROUP BY 子句**：
 - 在进行连接和过滤之后，对数据进行分组。如果多个表的列参与分组，分组会基于连接后的结果集。
 - 分组操作将影响后续聚合函数的计算，如 COUNT、SUM、AVG 等。

5.	**HAVING 子句**：
 - 对分组后的数据进一步筛选。与 WHERE 不同，HAVING 是在分组之后才应用的条件。

6.	**SELECT 子句**：
 - 提取需要展示的列。在连接查询中，可以选择来自不同表的列。
 - 如果使用了聚合函数，如 SUM 或 COUNT，这个阶段会计算这些聚合值。

7.	**ORDER BY 子句**：
 - 对结果集进行排序。排序可以基于某个表的字段，也可以基于计算或聚合后的结果。

8.	**LIMIT 子句**：
 - 最后，对结果集进行截取，限制返回的记录数量。

疑惑

1、明明先执行的on，为什么反而效率那么低呢

2、他们的区别在哪里

解疑之on与where的区别

ON 和 WHERE 条件在 SQL 查询中有着不同的作用和行为，尤其是在涉及多表连接时。理解它们的区别对优化查询性能和获取正确的结果非常重要。

基本概念

• ON 条件：
  • ON 条件用于连接（JOIN）操作，是在连接多个表时定义记录如何匹配的条件。它决定了哪些记录应该组合在一起，从而形成一个新的中间结果集。
  • ON 条件通常在 INNER JOIN、LEFT JOIN、RIGHT JOIN 等连接操作中使用。
• WHERE 条件：
  • WHERE 条件用于对查询的结果集进行过滤，是在整个连接操作之后应用的条件。
  • WHERE 条件对所有已连接的表的数据进行筛选，只有满足 WHERE 条件的记录才会出现在最终结果集中。

行为区别

1. 应用时机：
   • ON 条件在表连接时立即应用，它决定了哪些记录会被连接在一起，并进入下一步的处理。
   • WHERE 条件在连接操作之后应用，对连接后的结果集进行过滤。
2. 影响连接方式：
   • 在 INNER JOIN 中，ON 和 WHERE 条件的效果可能看起来类似，因为它们都会过滤出匹配的记录。但在 LEFT JOIN、RIGHT JOIN 等外连接中，ON 和 WHERE 条件的行为会明显不同。
   • ON 条件只影响连接匹配，但不会排除未匹配的左表（或右表）记录；而 WHERE 条件会过滤掉这些未匹配的记录。
3. 结果集差异：
   • 在 INNER JOIN 中：
     • ON 条件和 WHERE 条件可以互换，但 WHERE 条件通常是在连接之后应用，因此可能会有更直观的优化效果。
   • 在 LEFT JOIN 中：
     • ON 条件用于定义连接逻辑，未匹配的左表记录仍会保留在结果集中，即使右表记录不匹配，这些记录的右表字段为 NULL。
     • WHERE 条件则会过滤掉不满足条件的记录，这包括那些因为 LEFT JOIN 产生的 NULL 值。如果 WHERE 条件中包含了对右表字段的条件（比如 WHERE right_table.column IS NULL），则会影响左表记录是否保留。

示例说明

假设有两个表：

• students（学生表）：包含 student_id 和 name。
• enrollments（选课表）：包含 student_id 和 course_id。

1. 使用 ON 条件的 LEFT JOIN：

SELECT students.name, enrollments.course_id
FROM students
LEFT JOIN enrollments ON students.student_id = enrollments.student_id AND enrollments.course_id = 'Math'

• 结果：查询将返回所有学生。如果学生选修了 Math 课程，则显示 course_id 为 'Math'；否则，course_id 为 NULL。未选修 Math 的学生仍会出现在结果集中。

2. 使用 WHERE 条件的 LEFT JOIN：

SELECT students.name, enrollments.course_id
FROM students
LEFT JOIN enrollments ON students.student_id = enrollments.student_id
WHERE enrollments.course_id = 'Math'

• 结果：查询只返回那些选修了 Math 课程的学生，未选修 Math 的学生不会出现在结果集中。这是因为 WHERE 条件排除了那些 course_id 不为 'Math' 的记录，包括那些因 LEFT JOIN 产生的 NULL 记录。

关键点总结

1. ON 条件 是定义连接逻辑的一部分，影响记录如何匹配和组合。
2. WHERE 条件 是对整个结果集进行的过滤，它可以过滤掉不满足条件的记录。
3. 在外连接（如 LEFT JOIN）中，ON 和 WHERE 条件的区别尤为显著：ON 条件不会排除未匹配的左表记录，而 WHERE 条件可能会将这些记录排除在结果集之外。

理解 ON 和 WHERE 的区别，能够更好地控制 SQL 查询的行为，尤其在处理复杂连接时，选择合适的条件位置可以显著影响查询结果和性能。

重点说明

is null其实是解局的关键

1、首先左连接的时候我使用了is null这样如果我表中数据is null的很多，那么这个sql的执行过程其实是很复杂的，因为is null的模糊性

2、在左连接和where中我使用了右表某一个字段为is null不管如何是影响不了左表的，因为不管右表是否存在我要的都是is null。