连接表和过滤表时存在之间是否存在任何区别

Question

我有两个表A和B，并希望获得一个a的子集的子集，其键k也在b。

中

一个选项是使用加入

select A.*
from A
join B on A.k = B.k

另一个是

select A.*
from A
where exists (select *, B.k from B where A.k = B.k)

如果B中的k中的字段是唯一的，我觉得它们是一样的。对于Spark，该子查询确实存在吗？

Answer 1

最简单，最真实的方法是 explain查询并比较其物理计划。

scala> println(spark.version)
2.4.0
scala> sql("select A.* from A join B on A.k = B.k").explain
== Physical Plan ==
*(2) Project [k#10L]
+- *(2) BroadcastHashJoin [k#10L], [k#6L], Inner, BuildRight
   :- *(2) Project [id#8L AS k#10L]
   :  +- *(2) Range (0, 10, step=1, splits=8)
   +- BroadcastExchange HashedRelationBroadcastMode(List(input[0, bigint, false]))
      +- *(1) Project [id#4L AS k#6L]
         +- *(1) Range (0, 10, step=1, splits=8)
scala> sql("""select * from a where exists (select *, B.k from B where A.k = B.k)""").explain
== Physical Plan ==
*(2) Project [id#8L AS k#10L]
+- *(2) BroadcastHashJoin [id#8L], [k#6L], LeftSemi, BuildRight
   :- *(2) Range (0, 10, step=1, splits=8)
   +- BroadcastExchange HashedRelationBroadcastMode(List(input[0, bigint, false]))
      +- *(1) Project [id#4L AS k#6L, id#4L AS k#6L]
         +- *(1) Range (0, 10, step=1, splits=8)

他们看起来很一样，不是吗？

我觉得它们是相同的

它们如上所述。