Joining and combining DataFrames with the different join and union types

Spark SQL and DataFrame APIs support standard SQL join types plus union operations for combining datasets. Join type and key selection affect result cardinality and null handling, while broadcast joins optimize performance when one side is small.

1 · Learn the must-know

• 1Inner join returns only matching rows from both DataFrames; left (outer) join keeps all rows from the left side, filling unmatched right-side columns with null.
• 2Multiple join keys are specified by passing a list of column names or an AND-combined boolean expression, e.g. df1.join(df2, ['col1','col2']) or df1.join(df2, (df1.a==df2.a) & (df1.b==df2.b)).
• 3Broadcast join (broadcast() hint or automatic via spark.sql.autoBroadcastJoinThreshold) sends a small DataFrame to all executors to avoid a costly shuffle when joining with a much larger table.
• 4Cross join produces the Cartesian product of two DataFrames (every row paired with every row) and can explode row counts quickly, so it must be used deliberately, often via crossJoin() or an explicit CROSS JOIN.
• 5union() (or unionByName()) combines rows from two DataFrames with the same schema and does NOT remove duplicates, unlike SQL UNION which dedupes; use distinct() after union to mimic that behavior.
• 6unionByName() matches columns by name rather than position, which avoids silently misaligned data when column order differs between DataFrames, and can optionally allow missing columns with allowMissingColumns=True.

2 · Check your understanding