Joins

Overview

Learn how to merge data from two Spark DataFrames based on a common key, and understand the supported join types.

What You Will Learn

Detailed Concept Explanation

Joins combine columns from two DataFrames based on a shared key.

The syntax is: df1.join(df2, df1.key == df2.key, "join_type")

Supported join types:

• inner (default): Returns rows with matching keys in both DataFrames.
• left or left_outer: Returns all rows from the left DataFrame, plus matching rows from the right.
• right or right_outer: Returns all rows from the right DataFrame, plus matching rows from the left.
• outer or full: Returns all rows from both DataFrames, filling missing values with nulls.

Code Examples

Input Dataset Preview

Below are the users and orders datasets:

Users DataFrame:

Orders DataFrame:

Python (PySpark) Implementation

from pyspark.sql import SparkSession

spark = SparkSession.builder.appName("Joins").getOrCreate()
users = spark.createDataFrame([(1, "Alice"), (2, "Bob")], ["userId", "name"])
orders = spark.createDataFrame([(5001, 1, 99.9), (5002, 3, 49.9)], ["orderId", "customerId", "amount"])

# Perform an inner join on user ID
joined_df = users.join(orders, users.userId == orders.customerId, "inner")
joined_df.show()

Expected Output

+------+-----+-------+----------+------+
|userId| name|orderId|customerId|amount|
+------+-----+-------+----------+------+
|     1|Alice|   5001|         1|  99.9|
+------+-----+-------+----------+------+

Execution Plan Diagram (Python & Scala)

Scala Implementation

import org.apache.spark.sql.SparkSession

val spark = SparkSession.builder().appName("JoinsScala").getOrCreate()
import spark.implicits._

val users = Seq((1, "Alice"), (2, "Bob")).toDF("userId", "name")
val orders = Seq((5001, 1, 99.9), (5002, 3, 49.9)).toDF("orderId", "customerId", "amount")

val joined = users.join(orders, users("userId") === orders("customerId"), "inner")
joined.show()

Expected Output

+------+-----+-------+----------+------+
|userId| name|orderId|customerId|amount|
+------+-----+-------+----------+------+
|     1|Alice|   5001|         1|  99.9|
+------+-----+-------+----------+------+

SQL Implementation

SELECT u.userId, u.name, o.orderId, o.amount 
FROM users u 
INNER JOIN orders o ON u.userId = o.customerId;

Expected Output

Executing this SQL query returns:

Common Mistakes

• Key Name Ambiguity: If both DataFrames contain a column with the exact same name (e.g. id), referring to col("id") after the join will throw a "Reference is ambiguous" exception. To avoid this, rename the key columns before joining, or use the syntax: df1.join(df2, "id") which drops the duplicate key column automatically.

Best Practices

• Broadcast Small DataFrames: If one of the DataFrames is small (e.g. under 10MB), use a broadcast join (broadcast(small_df)) to copy the small dataset to all nodes, eliminating the need to shuffle the large dataset.

Interview Perspective

Interactive Challenges

Related Topics

• GroupBy Operations
• Union
• Broadcast Join