Combining datasets with join, union, intersect, and except

Azure Databricks supports SQL-style set and join operators in both PySpark DataFrame API and Spark SQL to combine or compare datasets. Joins merge columns from two DataFrames based on matching keys, while union, intersect, and except combine or compare rows across DataFrames with matching schemas.

1 · Learn the must-know

• 1DataFrame joins (join()) support types like inner, left, right, outer/full, left_semi, and left_anti, specified via the 'how' parameter, mirroring standard SQL JOIN semantics.
• 2union() combines rows from two DataFrames by position (not by column name) and requires both DataFrames to have the same number of columns with compatible data types; use unionByName() to align columns by name instead, which also supports an allowMissingColumns option.
• 3unlike SQL UNION, PySpark's union() does not remove duplicates by default—chain .distinct() after union() to deduplicate rows.
• 4intersect() returns only the distinct rows present in both DataFrames, while intersectAll() preserves duplicates by returning matching row counts based on multiplicity.
• 5exceptAll() (or subtract() in older APIs) returns rows in the first DataFrame not present in the second, respecting duplicate counts, whereas except()/exceptAll() differ in duplicate-handling similarly to intersect vs intersectAll.
• 6left_semi and left_anti joins are efficient alternatives to intersect and except for filtering rows in one DataFrame based on presence or absence of matches in another, without duplicating or bringing in columns from the right DataFrame.

2 · Check your understanding