Are there any drawbacks of using left join of smaller table with larger table vs inner join of two tables and right joining smaller table?

user_15839591 0
Viewed 98

Consider I have 2 tables,

  • A (1000 rows)
  • B (1B rows)

Left table: A Right table: B

I need to left join A with B, as I want all rows of A to remain in the output.

As expected, we can't broadcast A (to speed up) if I am doing a left join.

D = A.join(B,'key','left)

So, I have been doing an inner join of A & B, which allows broadcast. The intermediate output, say C, is much smaller table. I then left join A with C. In my experience, this is much much faster, and uses lesser resources as well.

C = B.join(F.broadcast(A),'key','inner')
D = C.join(A, 'key','right')

Is there any drawback to this approach? Missing out on edge cases?

pyspark apache-spark-sql apache-spark hive
1 Answers

If the key is unique in A, then this should work.

However, if the key is duplicated, you could increase the number of rows.

This is probably way this optimization is not built into the compiler for left join. Even if the keys are duplicated you could -- with a bit more effort -- get your approach to work.

user_11440350
Related