![在这里插入图片描述](https://img-blog.csdnimg.cn/direct/7c1d680f309b409da3c99c0e01dfb20d.gif#pic_center)
🎏:你只管努力,剩下的交给时间
🏠 :小破站
面试官: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
条件的记录才会出现在最终结果集中。
行为区别
-
应用时机:
ON
条件在表连接时立即应用,它决定了哪些记录会被连接在一起,并进入下一步的处理。WHERE
条件在连接操作之后应用,对连接后的结果集进行过滤。
-
影响连接方式:
- 在
INNER JOIN
中,ON
和WHERE
条件的效果可能看起来类似,因为它们都会过滤出匹配的记录。但在LEFT JOIN
、RIGHT JOIN
等外连接中,ON
和WHERE
条件的行为会明显不同。 ON
条件只影响连接匹配,但不会排除未匹配的左表(或右表)记录;而WHERE
条件会过滤掉这些未匹配的记录。
- 在
-
结果集差异:
- 在
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
记录。
关键点总结
ON
条件 是定义连接逻辑的一部分,影响记录如何匹配和组合。WHERE
条件 是对整个结果集进行的过滤,它可以过滤掉不满足条件的记录。- 在外连接(如
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
。