apache spark - How would I perform a join in Scala based on whether one OR another column match the case?

Question

Say I have df1 as this:

var df1 = Seq("a","b","c","d").toDF("letter")

and df2 as this:

var df2 = Seq(("a","1"),("q","2"),("x","c"),("d","z")).toDF("col1","col2")

I want to merge the two so that it returns the rows where EITHER col1 or col2 of df2 match the corresponding row of df1.

So the resulting dataframe should look like this: (Since row 2 of df2 doesn't have a "b" in it, it isn't returned in the resulting dataframe)

Thanks so much, have a great day and a happy new year!

Answer 1

You can use a semi join. It returns rows in df2 which satisfies the given condition.

val result = df2.join(
    df1,
    (df2.col("col1") === df1.col("letter")) || 
    (df2.col("col2") === df1.col("letter")),
    "semi"
)

result.show
+----+----+
|col1|col2|
+----+----+
|   a|   1|
|   x|   c|
|   d|   z|
+----+----+