Study guide
9.1 DataFrame Joins and Combining Datasets
Databricks Certified Associate Developer for Apache Spark — Free Study Guide 2026 Exam
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Spark Architecture and Execution
1.1 Spark Benefits, Modules, and DataFrame/Dataset Concepts 1.2 Driver, Executors, and Cluster Managers 1.3 Execution Hierarchy: Applications, Jobs, Stages, and Tasks 1.4 Lazy Evaluation, Transformations vs Actions, and Resilient Lineage
2 Chapter 2: Shuffles, Memory and Persistence
2.1 Narrow vs Wide Dependencies and the Shuffle Mechanism 2.2 Memory Management, Fault Tolerance, and Garbage Collection 2.3 Caching, Persistence, and StorageLevels
3 Chapter 3: Spark SQL Sessions, Tables and Views
3.1 SparkSession, Catalogs, Databases, and Namespaces 3.2 Managed vs External Tables and CTAS 3.3 Views: Temporary vs Global Temporary Views
4 Chapter 4: SQL Sources and Stored Layout
4.1 JDBC Reads, Writes, and Parallelism 4.2 Direct File SQL, Save Modes, and Persistent Layout
5 Chapter 5: SQL Queries and Analytics
5.1 SQL Queries, CTEs, Subqueries, and Built-in Expressions 5.2 Built-in SQL Expressions and an Integrated Query 5.3 Window Functions and Complex Joins in Spark SQL 5.4 SQL Join Semantics and Integrated Analytics
6 Chapter 6: DataFrame Construction and Columns
6.1 DataFrame Construction, Schemas, and StructTypes 6.2 Column Operations: Selecting, Renaming, Deriving, and Casting 6.3 Complex Nested Types and User-Defined Functions
7 Chapter 7: DataFrame Validation and Inspection
7.1 Row Operations: Filtering, Sorting, and Deduplication 7.2 Handling Missing and Null Data 7.3 Schema Inspection, Row Iteration, and List Conversion
8 Chapter 8: Aggregations and Dates
8.1 Grouping, Multi-Dimensional Aggregations, and Pivoting 8.2 Dates, Unix Epoch, and Time-Zone Conversions
9 Chapter 9: DataFrame Joins and File IO
9.1 DataFrame Joins and Combining Datasets 9.2 Data Sources, SaveModes, and Partitioning
10 Chapter 10: Shared Variables and Stateful Processing
10.1 Broadcast Variables, Accumulators, and Closures 10.2 Stateless UDFs, Stateful Operators, and StateStores
11 Chapter 11: Troubleshooting and Tuning
11.1 Diagnosing Performance with the Spark UI & Execution Plans 11.2 Data Skew and Partition Tuning: repartition vs coalesce 11.3 Join Optimization and Broadcast Hash Joins
12 Chapter 12: Structured Streaming
12.1 Streaming Architecture, Sources, and Sinks 12.2 Output Modes, Triggers, and Checkpointing 12.3 Event-Time Processing, Watermarking, and Windowing 12.4 Streaming Deduplication With and Without Watermarks
13 Chapter 13: Spark Connect and Deployment
13.1 Spark Connect Architecture & Client-Server Decoupling 13.2 Working with Spark Connect: Connections and Best Practices
14 Chapter 14: Pandas API on Spark
14.1 Pandas API on Apache Spark: Core Architecture and Syntax 14.2 Pandas-on-Spark Operations, Indexing, and Optimization
9.1 DataFrame Joins and Combining Datasets
Key Takeaways Explain This Topic AI
The join() method signature is df1.join(df2, on=..., how=...) , where how defaults to "inner" when omitted.
Joining on an equality expression (e.g., df1["id"] == df2["id"] ) retains duplicate join key columns from both DataFrames, causing an ambiguous reference error on subsequent column selection; joining on a column name string ( "id" ) suppresses the duplicate.
"left_semi" returns only columns from the left DataFrame for rows that match keys in the right DataFrame without multiplying rows; "left_anti" returns left rows that have no match in the right DataFrame.
PySpark union() and unionAll() are identical synonyms that concatenate DataFrames by column position rather than name, without deduplicating rows.
unionByName() resolves schemas by matching column names, and supports schema evolution across disjoint DataFrames when passed allowMissingColumns=True .
Last updated: October 2026
Joining and combining distributed datasets are fundamental operations in data engineering pipelines. In Apache Spark, joins require aligning records from two distinct DataFrames based on one or more key columns. Because the records for each dataset are distributed across cluster nodes, executing a join typically necessitates network data movement (shuffling) or data duplication (broadcasting). This section covers join syntax, join types, column ambiguity resolution, and set concatenation operations.
1. Join Syntax and Supported Join Types
The DataFrame join API in PySpark is invoked using the join() method: df_joined = df1.join(other=df2, on="customer_id", how="inner")
The how Parameter: Join Types
Spark SQL supports seven primary join types specified via the how argument. If how is omitted, Spark defaults to an "inner" join.
Join Type String ANSI SQL Equivalent Description
"inner" INNER JOIN Default. Returns rows where join keys match in both DataFrames. Non-matching rows from both sides are dropped.
"left" / "left_outer" LEFT OUTER JOIN Returns all rows from the left DataFrame. Matching right columns are populated; non-matching right columns are filled with null .
"right" / "right_outer" RIGHT OUTER JOIN Returns all rows from the right DataFrame. Non-matching left columns are filled with null .
"outer" / "full" / "full_outer" FULL OUTER JOIN Returns all rows from both DataFrames. Where keys do not match, missing columns from either side are filled with null .
"cross" CROSS JOIN Produces the Cartesian product of both datasets ( M × N M \times N M × N rows). Explicit crossJoin makes Cartesian intent clear; implicit-join policy is configurable.
"left_semi" WHERE EXISTS (...) Returns columns from the left DataFrame only where a matching key exists in the right DataFrame. Does not duplicate left rows if multiple right matches exist.
"left_anti" WHERE NOT EXISTS (...) Returns columns from the left DataFrame only where no matching key exists in the right DataFrame.
Semi and Anti Joins: Filtering Joins
Two of the most frequently tested join types on Databricks exams are "left_semi" and "left_anti" : # Left Semi Join: Keep customers who have placed at least one order
active_customers = customers_df.join(orders_df, on="customer_id", how="left_semi")
- # Left Anti Join: Keep customers who have never placed an order
- inactive_customers = customers_df.join(orders_df, on="customer_id", how="left_anti")
Key characteristics of semi and anti joins:
No Right Columns: The output schema contains only columns from the left DataFrame . No columns from the right DataFrame are appended.
No Row Multiplication: Even if a customer_id appears 50 times in orders_df , a left_semi join outputs that customer from customers_df exactly once (assuming customer_id was unique in customers_df ). This eliminates the need to run an expensive distinct() or dropDuplicates() operation after an inner join.
Catalyst Optimization: Spark optimizes semi/anti joins using broadcast hash joins or sort-merge joins with early termination, stopping the search as soon as the first match or non-match is confirmed.
2. Duplicate Join Column Names and Ambiguity
A frequent source of bugs in Spark pipelines occurs when joining two DataFrames that share identical column names. The mechanism used to define the on parameter determines whether duplicate columns are preserved or deduplicated.
Scenario A: Joining on a Column Name String
When you pass a column name string (or a list of strings) to the on parameter, Spark recognizes that the join key exists in both DataFrames. It merges the keys and outputs exactly one instance of the column: # Clean join: Exactly one 'id' column exists in df_clean
- df_clean = df1.join(df2, on="id", how="inner")
- df_clean.select("id", "name", "department").show()
- Scenario B: Joining on a Boolean Column Expression
When you define the join condition using an equality expression (such as df1["id"] == df2["id"] or col("df1.id") == col("df2.id") ), Spark retains both columns in the output DataFrame: # Ambiguous join: Both df1.id and df2.id exist in the result
df_ambiguous = df1.join(df2, df1["id"] == df2["id"], how="inner")
# This call throws AnalysisException!
df_ambiguous.select("id").show()
# AnalysisException: Reference 'id' is ambiguous, could be: df1.id, df2.id.
Three Strategies to Resolve Column Ambiguity
Drop the duplicate right-hand column directly: df_resolved = df1.join(df2, df1["id"] == df2["id"]).drop(df2["id"])
Rename the join column in one DataFrame prior to the join: df2_renamed = df2.withColumnRenamed("id", "dept_id")
- df_resolved = df1.join(df2_renamed, df1["id"] == df2_renamed["dept_id"])
- Use DataFrame aliases and reference qualified columns: df_resolved = df1.alias("a").join(
- df2.alias("b"),
- F.col("a.id") == F.col("b.id")
- ).select("a.id", "a.name", "b.department")
- 3. Cartesian Products and Cross Joins
A cross join computes the Cartesian product of two DataFrames, pairing every row of the left dataset with every row of the right dataset. If DataFrame A A A contains 10,000 rows and DataFrame B B B contains 50,000 rows, their cross join produces 10,000 × 50,000 = 500,000,000 10{,}000 \times 50{,}000 = 500{,}000{,}000 10 , 000 × 50 , 000 = 500 , 000 , 000 rows. # Explicit cross join syntax
df_cross = df1.crossJoin(df2)
- # Or using join with how='cross'
- df_cross = df1.join(df2, how="cross")
Caution
Upstream Spark 3.5 enables implicit Cartesian joins by default through spark.sql.crossJoin.enabled=true. With the setting false, an implicit Cartesian join can fail analysis. Use explicit crossJoin() when intended and estimate the M × N result size first.
4. Combining Datasets: union() , unionAll() , and unionByName()
When combining datasets vertically by appending rows, Spark provides two primary approaches: positional concatenation and name-based concatenation.
union() vs. unionAll()
In PySpark, union() and unionAll() are identical functions . In early versions of Apache Spark, unionAll() was the original method name borrowed from SQL. Starting in Spark 2.0, union() was introduced as a cleaner alias, and unionAll() was retained for backwards compatibility. # union() and unionAll() behave identically in PySpark
combined_df = df1.union(df2)
Important
SQL versus Python method names: In Spark SQL as in standard SQL, UNION removes duplicate rows, whereas UNION ALL retains duplicates. In Apache Spark, both union() and unionAll() retain duplicates . To achieve deduplication, you must explicitly call .distinct() or .dropDuplicates() after the union.
The Positional Schema Trap
df1.union(df2) binds columns strictly by ordinal position , completely ignoring column names: # df1 schema: (name: string, age: integer)
# df2 schema: (age: integer, name: string)
# This produces corrupted or failing data!
corrupted_df = df1.union(df2)
Because Spark matches by column position, it attempts to insert df2.age into the name column and df2.name into the age column. If data types are incompatible, Spark throws an AnalysisException ; if types are compatible (e.g., two string columns swapped), Spark silently swaps the data, resulting in silent corruption.
Resilient Combination: unionByName()
To safely combine DataFrames regardless of column order, use unionByName() : # Automatically matches columns by name
- safe_df = df1.unionByName(df2)
- Schema Evolution: allowMissingColumns=True
In modern Spark pipelines, schemas frequently evolve over time. If df1 contains columns [id, name, email] and df2 contains columns [id, name, phone] , a standard unionByName() call fails with an AnalysisException stating that schemas do not match.
- Setting allowMissingColumns=True enables graceful schema evolution: # Handles disjoint schemas automatically
- evolved_df = df1.unionByName(df2, allowMissingColumns=True)
The resulting DataFrame schema contains the union of all columns: [id, name, email, phone] . Rows originating from df1 have null in the phone column, and rows from df2 have null in the email column.
Joining on multiple keys
Use a list of shared key names for a multi-column equality join: orders.join(customers, ["customer_id", "region"], "left"). Both equality conditions must hold, and the named key columns appear once. For different names or range conditions, use parenthesized Column expressions combined with & and aliases. Normal equality does not match null keys; eqNullSafe() does when missing-to-missing matching is intended. Null-safe matching can create large matches when both sides contain many missing keys, so validate key cardinality before using it.