left join详解

left join详解LEFT JOIN 详解一、基本语法二、执行逻辑与结果特点三、示例说明四、与其他 JOIN 的对比五、ON 条件与 WHERE 条件的区别重要六、多表 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。二、执行逻辑与结果特点第一步取左表的所有行。第二步对左表的每一行在右表中查找满足ON条件的行。如果找到一条或多条匹配行则与左表行组合输出一行或多行匹配多少条输出多少条。如果一条都没找到则结果中右表的所有字段填充NULL但仍输出该左表行。第三步如果左表有重复行结果中也会保留重复不去重。核心特点左表行数永远是被完整保留的右表只贡献匹配的行或NULL。三、示例说明假设有以下两张表学生表studentsstudent_idname1张三2李四3王五成绩表scoresstudent_idsubjectscore1数学901英语852数学78查询每个学生及其成绩即使没有成绩也要显示SELECTs.student_id,s.name,sc.subject,sc.scoreFROMstudents sLEFTJOINscores scONs.student_idsc.student_id;结果student_idnamesubjectscore1张三数学901张三英语852李四数学783王五NULLNULL学生 3王五没有成绩右表字段为NULL但左表行仍被保留。学生 1 有两条成绩所以输出两行。四、与其他 JOIN 的对比JOIN 类型返回结果INNER JOIN只返回两个表中都匹配的行LEFT JOIN返回左表全部行右表无匹配时补NULLRIGHT JOIN返回右表全部行左表无匹配时补NULL可被LEFT JOIN通过调换表顺序替代FULL OUTER JOIN返回两表全部行对方无匹配时补NULLMySQL 不直接支持CROSS JOIN笛卡尔积不指定条件五、ON 条件与 WHERE 条件的区别重要ON 条件决定如何连接两个表在连接过程中应用。对于LEFT JOINON 中的右表条件不会过滤掉左表行只会影响右表是否匹配。WHERE 条件在连接完成之后对最终结果集进行过滤。如果 WHERE 条件中包含右表的列并且要求该列非空实际上会将左连接转换为内连接因为 NULL 会被过滤掉。错误示例想找没有成绩的学生但误用了 WHERESELECTs.student_id,s.nameFROMstudents sLEFTJOINscores scONs.student_idsc.student_idWHEREsc.scoreISNULL;-- 正确只保留无成绩的学生危险示例想找成绩大于 80 的学生但误写为SELECTs.student_id,s.name,sc.scoreFROMstudents sLEFTJOINscores scONs.student_idsc.student_idWHEREsc.score80;这样会丢失成绩为 NULL 的学生即无成绩的学生而且学生 2 成绩 78 也会被过滤。结果与INNER JOIN相同但可能不符合预期。六、多表LEFT JOIN可以连续使用多个LEFT JOINSELECT*FROMtable1LEFTJOINtable2ONt1.keyt2.keyLEFTJOINtable3ONt1.keyt3.key;每个LEFT JOIN都以其左侧的当前结果集为左表依次连接。注意当连接多个表时ON条件中的列可能来自前面任何已经连接的表。七、性能考虑LEFT JOIN通常比INNER JOIN稍慢因为它必须保留左表所有行即使右表没有匹配。在右表的连接列上创建索引可以显著提升性能。尽量避免在大表上使用LEFT JOIN后再用WHERE过滤右表的非空列这种情况下通常INNER JOIN更合适。八、常见应用场景主表可选明细如查询所有员工及其所属部门即使有些员工未分配部门。查找缺失关联通过WHERE 右表主键 IS NULL找出左表中在右表没有对应记录的行。SELECTs.*FROMstudents sLEFTJOINscores scONs.student_idsc.student_idWHEREsc.student_idISNULL;-- 找出没有成绩的学生生成报表保留所有分类如月份、产品类别即使某些分类没有数据。避免因右表无匹配而丢失左表行。九、与其他数据库的差异MySQL、PostgreSQL、SQL Server、Oracle语法和语义完全一致。Hive/Spark SQL支持LEFT JOIN但需要注意 NULL 处理及 ON 条件中不允许出现复杂表达式某些版本有限制。SQLite完全支持。十、小结要点说明保留左表全部行即使右表没有匹配左表行也会出现右表无匹配时为 NULL右表所有列填充 NULLON vs WHEREON 决定连接WHERE 过滤最终结果WHERE 右表列条件可能“破坏”左连接语义性能索引右表连接列避免不必要的左连接常见用途查找缺失关联、保留主表全部信息掌握LEFT JOIN是 SQL 进阶的关键一步它让你能够安全地保留基准表的所有记录同时根据需要关联补充信息。