前言

此题是2025年10月携程最新的数据开发面试题，我查阅了一下《大数据开发面试笔记V7.0》，确实是没有！但是回答本题又有一定的难度，于是今天来给大家解析一下本题，并且会补充在面试笔记中（位于spark补充题第29题）

参考答案

非等值连接不是广播JOIN，而是广播嵌套循环JOIN，也叫 BroadcastNestedLoopJoin，虽然也会将小表广播到所有的executor上，不同于BroadcastHashJoin的是，在JOIN时会用大表的key在未排序的小表中进行逐一匹配，效率非常低下

示例说明

spark.sql(
  """
    |select 1 as id, "a" as name1
    |union all
    |select 2 as id, "b" as name1
    |union all
    |select 3 as id, "c" as name1
    |union all
    |select 4 as id, "d" as name1
    |union all
    |select 5 as id, "e" as name1
    |union all
    |select 6 as id, "f" as name1
    |""".stripMargin).createOrReplaceTempView("t1")

spark.sql(
  """
    |select 1 as id, "aa" as name2
    |union all
    |select 2 as id, "bb" as name2
    |union all
    |select 3 as id, "cc" as name2
    |union all
    |select 7 as id, "xx" as name2
    |""".stripMargin).createOrReplaceTempView("t2")

等值连接

spark.sql(
  """
    |select *
    |from t1
    |left join t2
    |on t1.id = t2.id
    |""".stripMargin).show(false)

非等值连接

spark.sql(
  """
    |select *
    |from t1
    |left join t2
    |on t1.id != t2.id
    |""".stripMargin).show(false)

思考

大小表非等值连接如何避免使用BroadcastNestedLoopJoin呢？