文章目录title: 【四_(1)】Hive/Spark SQL 七种 JOIN 的区别NULL 匹配规则与结果对比 date: 2026-05-18T22:16:4008:00 draft: false tags: [HiveSQL, FULL OUTER JOIN, CROSS JOIN, LEFT SEMI JOIN] categories: [Hive] description: 通过 orders 和 users 示例表对比 Hive/Spark SQL 中 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN、CROSS JOIN、LEFT SEMI JOIN 和 LEFT ANTI JOIN 的匹配规则与结果差异。1、数据源2、NULL 值判断3、JOIN 类别3.1 INNER JOIN3.2 LEFT JOIN3.3 RIGHT JOIN3.4 FULL OUTER JOIN3.5 CROSS JOIN3.6 LEFT SEMI JOIN3.7 LEFT ANTI JOINSpark SQL3.8 汇总不同 JOIN 的行为4、NULL-safe equality我的网站原文 https://eleanora-lyh.github.io/MyLearningNotes/csdn处的文章会尽快同步更新欢迎大家来访问title: “【四_(1)】Hive/Spark SQL 七种 JOIN 的区别NULL 匹配规则与结果对比”date: 2026-05-18T22:16:4008:00draft: falsetags: [“HiveSQL”, “FULL OUTER JOIN”, “CROSS JOIN”, “LEFT SEMI JOIN”]categories: [“Hive”]description: “通过 orders 和 users 示例表对比 Hive/Spark SQL 中 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL OUTER JOIN、CROSS JOIN、LEFT SEMI JOIN 和 LEFT ANTI JOIN 的匹配规则与结果差异。”1、数据源连接 Hive 数据库创建示例表并填充数据createtableorders(user_id string,amountdouble)rowformat delimitedfieldsterminatedby\tstoredastextfile;insertintoordersvalues(1,100),(NULL,200),(3,300);createtableusers(user_id string,user_name string)rowformat delimitedfieldsterminatedby\tstoredastextfile;insertintousersvalues(1,Alice),(2,Bob),(NULL,Charlie);select*fromorders;select*fromusers;ordersuser_idamount1100NULL2003300usersuser_iduser_name1Alice2BobNULLCharlie2、NULL 值判断NULL和 任意值比较的结果都是UNKNOWN如下表达式结果NULL 1UNKNOWNNULL NULLUNKNOWN∵ Join 只有在ON条件结果为TRUE时才匹配且NULL NULL的结果不是TRUE而是UNKNOWN∴ 因此普通等值 Join 中NULL 不会与 NULL 匹配但不同 Join 类型对于“未匹配行是否保留”有区别3、JOIN 类别3.1 INNER JOININNER JOIN只会保留双方都匹配上的行当o.user_id或u.user_id为NULL时等值条件返回UNKNOWN因此未匹配的相关行被过滤SELECT*FROMorders oINNERJOINusers uONo.user_idu.user_id;结果user_idamountuser_iduser_name1100.01Alice3.2 LEFT JOINLEFT JOIN保留完整的左表以及匹配上的右表右表未匹配上的列会补上NULL左表 KeyNULL 时无法匹配右表但由于是 Left Join左表记录仍然保留右表所有字段补 NULLSELECT*FROMorders oLEFTJOINusers uONo.user_idu.user_id;user_idamountuser_iduser_name1100.01AliceNULL200.0NULLNULL3300.0NULLNULL3.3 RIGHT JOINRIGHT JOIN保留完整的右表以及匹配上的左表左表未匹配上的列会补上NULL右表KeyNULL时无法匹配左表但由于是RIGHT JOIN右表记录仍然保留左表所有字段补NULLSELECT*FROMorders oRIGHTJOINusers uONo.user_idu.user_id;user_idamountuser_iduser_name1100.01AliceNULLNULL2BobNULLNULLNULLCharlie3.4 FULL OUTER JOINFULL OUTER JOIN保留左表所有行和右表所有行能根据 ON 条件匹配上的行合并成一行无法匹配的行缺失的一侧用 NULL 填充左或右表 KeyNULL 时无法匹配则另一边所有字段都补 NULLSELECT*FROMorders oFULLOUTERJOINusers uONo.user_idu.user_id;结果user_idamountuser_iduser_name1100.01AliceNULLNULL2Bob3300.0NULLNULLNULLNULLNULLCharlieNULL200.0NULLNULLuser_id1 在两表中都存在 → 正常匹配合并为一行user_idNULL (amount200)在左表右表也有一个user_idNULL的行但NULL NULL的结果是UNKNOWN而不是TRUE所以两行不会匹配左表行单独出现且右表字段填NULLuser_id3 只在左表 → 保留左表右表填 NULLuser_id2 只在右表 → 保留右表左表填 NULLuser_idNULL (Charlie) 在右表同理它没有匹配到左表的任何行包括左表的 NULL 行所以右表这一行单独出现左表填 NULL3.5 CROSS JOINCROSS JOIN返回两表的笛卡尔积左表的每一行都会与右表的每一行组合。本例两表各有 3 行因此返回3 × 3 9 3 \times 3 93×39行NULL不参与匹配判断也不会阻止组合。SELECT*FROMorders oCROSSJOINusers u;结果user_idamountuser_iduser_name1100.01Alice1100.02Bob1100.0NULLCharlieNULL200.01AliceNULL200.02BobNULL200.0NULLCharlie3300.01Alice3300.02Bob3300.0NULLCharlieSQL 在没有ORDER BY时不保证结果顺序表格中的顺序仅用于展示。3.6 LEFT SEMI JOINLEFT SEMI JOIN只返回左表中能与右表匹配的行且只输出左表的列相当于 WHERE EXISTS。SELECTo.*FROMorders oLEFTSEMIJOINusers uONo.user_idu.user_id;结果只有左表能匹配的行被保留user_idamount1100.0orders.user_idNULL与右表的NULL在普通等值条件下不匹配user_id3在右表中也不存在因此这两行不会出现在结果中。3.7 LEFT ANTI JOINSpark SQLLEFT ANTI JOIN只返回左表中不能与右表匹配的行并且只输出左表的列语义相当于WHERE NOT EXISTS。Spark SQL 原生支持LEFT ANTI JOIN但 Hive 的 JOIN 语法不包含该类型。Hive 0.13 及以上版本可以使用NOT EXISTS表达相同语义。实际执行策略和性能取决于优化器、数据规模、数据分布及是否能够广播右表不能仅凭 JOIN 写法断定一定会减少 Shuffle。SELECTo.*FROMorders oLEFTANTIJOINusers uONo.user_idu.user_id;结果user_idamountNULL2003300Hive 中的等价写法SELECTo.*FROMorders oWHERENOTEXISTS(SELECT1FROMusers uWHEREo.user_idu.user_id);3.8 汇总不同 JOIN 的行为Join 类型未匹配 NULL 行是否保留INNER JOIN不保留LEFT JOIN保留左表 NULL Key 行RIGHT JOIN保留右表 NULL Key 行FULL OUTER JOIN左右两边分别保留LEFT SEMI JOIN普通等值条件下不保留左表 NULL Key 行LEFT ANTI JOIN普通等值条件下保留左表 NULL Key 行FULL OUTER JOIN 会同时保留左右表的所有行并用 NULL 填充未匹配的另一侧。对比结果FULL OUTER JOIN 结果之前已经算过o.user_ido.amountu.user_idu.user_name11001AliceNULL200NULLNULL3300NULLNULLNULLNULL2BobNULLNULLNULLCharlie可以看到SEMI 只取了左表匹配行第1行ANTI 取了左表未匹配行第2、3行FULL 保留了所有左表行和右表行4、NULL-safe equalityHive / Spark SQL/ MySQL 通常可以使用表示 NULL-safe equality条件结果NULL 1FALSENULL NULLTRUE因此a.keyb.key相当于以下条件a.keyb.keyOR(a.keyISNULLANDb.keyISNULL)SQL Server 2022 及以上版本还可以使用a.key IS NOT DISTINCT FROM b.key旧版本可以使用上面的显式条件。