left join详解

1.不考虑where条件下,left join 会把左表所有数据查询出来，on及其后面的条件仅仅会影响右表的数据(符合就显示,不符合全部为null)

2.在匹配阶段，where子句的条件都不会被使用，仅在匹配阶段完成以后，where子句条件才会被使用，它将从匹配阶段产生的数据中检索过滤

3.所以左连接关注的是左边的主表数据，不应该把on后面的从表中的条件加到where后，这样会影响原有主表中的数据

4.where后面：是先连接然生成临时查询结果，然后再筛选

on后面：先根据条件过滤筛选，再连接生成临时查询结果

5.对于条件在on加个and还是用子查询，查询结果是一模一样的，至于如何使用这个需要分情况，用子查询的话会多一个maptask，但是如果利用这个子查询能过滤到很多数据的话，用子查询还是比较建议的，因为不会加载太多的数据到内存中，如果过滤数据不多的情况下，建议用on后面加and条件

LEFT JOIN详解

LEFT JOIN（也称为LEFT OUTER JOIN）是 SQL 中最常用的连接类型之一。它返回左表中的所有行，以及右表中与左表匹配的行；如果右表中没有匹配，则结果中右表部分返回NULL。

一、基本语法

SELECT列名列表FROM左表表名LEFTJOIN右表表名ON连接条件;

• 左表：FROM子句中指定的表，结果集中会保留它的全部行。
• 右表：LEFT JOIN后面指定的表，只有满足连接条件的行才会被包含。
• ON 条件：指定左表和右表如何关联（如左表.id = 右表.id）。
• 可选关键字：OUTER可省略，LEFT OUTER JOIN等价于LEFT JOIN。

二、执行逻辑与结果特点

1. 第一步：取左表的所有行。
2. 第二步：对左表的每一行，在右表中查找满足ON条件的行。
   • 如果找到一条或多条匹配行，则与左表行组合，输出一行或多行（匹配多少条输出多少条）。
   • 如果一条都没找到，则结果中右表的所有字段填充NULL，但仍输出该左表行。
3. 第三步：如果左表有重复行，结果中也会保留重复（不去重）。

核心特点：左表行数永远是被完整保留的，右表只贡献匹配的行或NULL。

三、示例说明

假设有以下两张表：

学生表students：

成绩表scores：

查询：每个学生及其成绩（即使没有成绩也要显示）：

SELECTs.student_id,s.name,sc.subject,sc.scoreFROMstudents sLEFTJOINscores scONs.student_id=sc.student_id;

结果：

• 学生 3（王五）没有成绩，右表字段为NULL，但左表行仍被保留。
• 学生 1 有两条成绩，所以输出两行。

四、与其他 JOIN 的对比

五、ON 条件与 WHERE 条件的区别（重要！）

• ON 条件：决定如何连接两个表，在连接过程中应用。对于LEFT JOIN，ON 中的右表条件不会过滤掉左表行（只会影响右表是否匹配）。
• WHERE 条件：在连接完成之后，对最终结果集进行过滤。如果 WHERE 条件中包含右表的列，并且要求该列非空，实际上会将左连接转换为内连接（因为 NULL 会被过滤掉）。

错误示例：想找没有成绩的学生，但误用了 WHERE：

SELECTs.student_id,s.nameFROMstudents sLEFTJOINscores scONs.student_id=sc.student_idWHEREsc.scoreISNULL;-- 正确：只保留无成绩的学生

危险示例：想找成绩大于 80 的学生，但误写为：

SELECTs.student_id,s.name,sc.scoreFROMstudents sLEFTJOINscores scONs.student_id=sc.student_idWHEREsc.score>80;

这样会丢失成绩为 NULL 的学生（即无成绩的学生），而且学生 2 成绩 78 也会被过滤。结果与INNER JOIN相同，但可能不符合预期。

六、多表LEFT JOIN

可以连续使用多个LEFT JOIN：

SELECT*FROMtable1LEFTJOINtable2ONt1.key=t2.keyLEFTJOINtable3ONt1.key=t3.key;

每个LEFT JOIN都以其左侧的当前结果集为左表，依次连接。

注意：当连接多个表时，ON条件中的列可能来自前面任何已经连接的表。

七、性能考虑

• LEFT JOIN通常比INNER JOIN稍慢，因为它必须保留左表所有行，即使右表没有匹配。
• 在右表的连接列上创建索引可以显著提升性能。
• 尽量避免在大表上使用LEFT JOIN后再用WHERE过滤右表的非空列，这种情况下通常INNER JOIN更合适。

八、常见应用场景

1. 主表+可选明细：如查询所有员工及其所属部门（即使有些员工未分配部门）。
2. 查找缺失关联：通过WHERE 右表主键 IS NULL找出左表中在右表没有对应记录的行。

   SELECTs.*FROMstudents sLEFTJOINscores scONs.student_id=sc.student_idWHEREsc.student_idISNULL;-- 找出没有成绩的学生

3. 生成报表：保留所有分类（如月份、产品类别），即使某些分类没有数据。
4. 避免因右表无匹配而丢失左表行。

九、与其他数据库的差异

• MySQL、PostgreSQL、SQL Server、Oracle：语法和语义完全一致。
• Hive/Spark SQL：支持LEFT JOIN，但需要注意 NULL 处理及 ON 条件中不允许出现复杂表达式（某些版本有限制）。
• SQLite：完全支持。

十、小结

掌握LEFT JOIN是 SQL 进阶的关键一步，它让你能够安全地保留基准表的所有记录，同时根据需要关联补充信息。