Orcale、MySQL中左连接，右连接，内连接的区别以及运用场景系列04（运用场景、左右表关系）
1. 总结
Oracle
| 连接类型 | 区别 | 运用场景 | 左右表关系适用性 | ON与WHERE条件相关 |
|---|---|---|---|---|
内连接（INNER JOIN） | 仅返回两个表中满足连接条件的行，不满足条件的行不会出现在结果集中。例如，若有employees表和departments表，仅返回两表中department_id匹配的行 | 当需要获取两个表中相互关联的数据时使用。如在员工管理系统中，查询有部门归属的员工信息 | 适用于一对一、多对一、多对多关系。一对一关系下返回精确匹配的一对一记录；多对一关系中返回匹配的多对一组合；多对多关系返回所有匹配组合 | ON关键字用于在连接操作阶段指定连接条件，决定如何将不同表的行进行匹配。WHERE在连接完成后对结果集进行过滤，可筛选出满足特定条件的记录 |
左连接（LEFT JOIN或LEFT OUTER JOIN） | 返回左表的全部行，以及右表中匹配的行。若右表无匹配行，对应列显示为NULL。例如，employees表左连接departments表，employees表的所有行都会出现，无部门归属的员工对应的部门信息为NULL | 以左表为主，获取左表所有记录并关联右表信息。如查看所有员工及其所属部门信息，即使部分员工无部门归属 | 适用于各种关系。一对一关系中保留左表所有记录；多对一关系保留左表（如员工表）全部记录；多对多关系保留左表（如学生表）所有记录 | ON决定左右表连接时的匹配规则，即使不满足条件左表行也会保留。WHERE对连接后的结果进一步筛选，可过滤含NULL的行 |
右连接（RIGHT JOIN或RIGHT OUTER JOIN） | 返回右表的全部行，以及左表中匹配的行。若左表无匹配行，对应列显示为NULL。例如，employees表右连接departments表，departments表的所有行都会出现，无员工的部门对应的员工信息为NULL | 以右表为主，获取右表所有记录并关联左表信息，不过实际中常通过交换表顺序用左连接替代 | 适用于各种关系。一对一关系保留右表所有记录；多对一关系保留右表（如部门表）全部记录；多对多关系保留右表（如课程表）所有记录 | ON用于确定右表与左表连接的匹配条件，不满足条件右表行保留。WHERE在连接结果基础上筛选 |
MySQL
| 连接类型 | 区别 | 运用场景 | 左右表关系适用性 | ON与WHERE条件相关 |
|---|---|---|---|---|
内连接（INNER JOIN） | 只返回两个表中满足连接条件的行，不满足连接条件的行被排除。比如customers表和orders表内连接，仅返回有订单的客户记录 | 需要获取两个表中相互关联的数据时使用。如在电商系统中，查询有购买记录的客户信息 | 适用于一对一、多对一、多对多关系。一对一关系返回匹配的一对一数据；多对一关系返回匹配的多对一组合；多对多关系返回所有匹配的组合 | ON用于在连接操作时指定连接条件，确定不同表行的匹配方式。WHERE在连接完成后对结果集进行过滤，筛选出符合特定条件的记录 |
左连接（LEFT JOIN或LEFT OUTER JOIN） | 返回左表的所有行，右表中匹配的行与之组合，右表无匹配行时对应列值为NULL。例如，products表左连接reviews表，products表的所有产品都会出现，无评价的产品对应的评价信息为NULL | 以左表为主，获取左表全部记录并关联右表信息。如查看所有产品及其评价信息，即使部分产品无评价 | 适用于各种关系。一对一关系保留左表所有记录；多对一关系保留左表（如产品表）全部记录；多对多关系保留左表（如用户表）所有记录 | ON决定左右表连接时的匹配规则，不满足条件左表行仍保留。WHERE对连接后的结果进一步筛选，可去除含NULL的行等 |
右连接（RIGHT JOIN或RIGHT OUTER JOIN） | 返回右表的所有行，左表中匹配的行与之组合，左表无匹配行时对应列值为NULL。例如，orders表右连接payment_methods表，payment_methods表的所有支付方式都会出现，无对应订单的支付方式对应的订单信息为NULL | 以右表为主，获取右表所有记录并关联左表信息，实际中常用左连接替代 | 适用于各种关系。一对一关系保留右表所有记录；多对一关系保留右表（如支付方式表）全部记录；多对多关系保留右表（如活动表）所有记录 | ON用于确定右表与左表连接的匹配条件，不满足条件右表行保留。WHERE在连接结果基础上进行筛选 |
2. 左右表关系
一对一关系
- 解释：左表中的每一行在右表中最多只有一行与之匹配，反之亦然。例如，一个员工对应一个工号，员工表和工号表就是一对一关系。
- 连接应用：三种连接方式都可以使用，根据具体需求选择。如果只需要匹配的记录，使用内连接；如果要保留左表或右表的所有记录，分别使用左连接或右连接。
多对一关系
- 解释：左表中的多行可以对应右表中的一行。例如，多个员工属于同一个部门，员工表和部门表就是多对一关系。
- 连接应用：内连接会返回所有匹配的员工 - 部门组合；左连接会返回所有员工的信息以及对应的部门信息，若员工没有关联部门则部门信息为
NULL；右连接会返回所有部门信息以及对应的员工信息，若部门没有员工则员工信息为NULL。
多对多关系
- 解释：左表中的多行可以对应右表中的多行，通常需要通过一个中间表来实现关联。例如，学生和课程之间是多对多关系，一个学生可以选多门课程，一门课程可以被多个学生选，通过选课表来关联学生表和课程表。
- 连接应用：内连接会返回所有学生 - 课程的匹配组合；左连接会保留所有学生的记录，即使学生没有选课；右连接会保留所有课程的记录，即使课程没有学生选。
3. 运用场景
内连接的运用场景
- 数据验证和匹配：当你需要确保从两个表中获取的数据是相互关联且有效的时候使用。例如，在一个电商系统中，你想查询所有有订单的用户信息，就可以通过内连接将
users表和orders表连接起来，只返回那些既存在于users表又有对应订单记录的用户。 - 数据统计和分析：在进行数据分析时，通常只对两个表中有关联的数据感兴趣。比如，统计每个部门的员工平均工资，通过内连接将
employees表和departments表连接，只考虑有部门归属的员工。
左连接的运用场景
- 主表数据完整性查询：当你以某个表为主，需要获取该表的所有记录，同时关联其他表的信息时使用。例如，在一个学校管理系统中，你想查看所有学生的信息以及他们所选课程的信息，如果有些学生还没有选课，使用左连接可以确保所有学生的信息都被包含在结果中，没选课的学生对应的课程信息显示为
NULL。 - 查找未匹配的数据：可以通过左连接找出左表中哪些记录在右表中没有匹配项。例如，在一个会员系统中，你想找出哪些会员还没有消费记录，通过将会员表和消费记录表进行左连接，筛选出消费记录为
NULL的会员。
右连接的运用场景
右连接的使用场景和左连接类似，只是主表变成了右表。不过在实际应用中，右连接使用相对较少，因为大多数情况下可以通过交换表的顺序使用左连接来达到相同的效果。例如，当你需要以某个表为基准，查看该表所有记录以及与之关联的其他表信息，且该表在逻辑上更适合作为右表时可以使用右连接。
重要消息：为了方便不同阶段的同学学习和收藏，我将该文章拆分成了一个系列，有兴趣的同学可以关注我持续学习。在为企业级的开发做培训的过程中，往往能够感受到，大多数开发是没有自己的知识体系，难以有很大的成长空间，学习不是一蹴而就的，往往需要不断积累和梳理自己的知识体系。筛选和淘汰，是企业级开发的必经之路，也是信息部门或是信息公司的运行机制。