Multi-column joins in Apache Spark - Step-by-Step Execution

Concept Flow - Multi-column joins

Start with two DataFrames

↓

Specify join columns

↓

Perform join on multiple columns

↓

Result: DataFrame with matched rows

↓

Handle unmatched rows if needed (outer, left, right)

↓

End

We start with two tables, pick columns to match on, join them by all those columns, and get combined rows where all column values match.

Execution Sample

Apache Spark

df1.join(df2, on=["id", "date"], how="inner")

Join two DataFrames on columns 'id' and 'date' keeping only rows where both match.

Execution Table

Step	Action	df1 Row	df2 Row	Join Condition (id,date)	Result Row Included?
1	Check df1 row (id=1, date=2023-01-01) against df2 rows	(1, 2023-01-01, A)	(1, 2023-01-01, X)	Match	Yes
2	Check df1 row (id=1, date=2023-01-01) against df2 rows	(1, 2023-01-01, A)	(2, 2023-01-02, Y)	No Match	No
3	Check df1 row (id=2, date=2023-01-02) against df2 rows	(2, 2023-01-02, B)	(1, 2023-01-01, X)	No Match	No
4	Check df1 row (id=2, date=2023-01-02) against df2 rows	(2, 2023-01-02, B)	(2, 2023-01-02, Y)	Match	Yes
5	Check df1 row (id=3, date=2023-01-03) against df2 rows	(3, 2023-01-03, C)	(1, 2023-01-01, X)	No Match	No
6	Check df1 row (id=3, date=2023-01-03) against df2 rows	(3, 2023-01-03, C)	(2, 2023-01-02, Y)	No Match	No
7	End of rows

💡 All df1 rows checked against df2 rows; only rows with matching id and date included.

Variable Tracker

Variable	Start	After Step 1	After Step 4	Final
df1 current row	None	(1, 2023-01-01, A)	(2, 2023-01-02, B)	(3, 2023-01-03, C)
df2 current row	None	(1, 2023-01-01, X)	(2, 2023-01-02, Y)	(2, 2023-01-02, Y)
Result rows count	0	1	2	2

Key Moments - 2 Insights

Why do some rows from df1 not appear in the result?

Because the join is inner and requires matching values in all join columns (id and date). Rows without exact matches in df2 are excluded, as shown in execution_table rows 5 and 6.

What happens if only one join column matches but not the other?

The row is not included because multi-column join requires all specified columns to match simultaneously.

Visual Quiz - 3 Questions

Look at the execution table, how many rows are included in the join result?

A 3

B 2

C 4

D 1

💡 Hint

Check the 'Result Row Included?' column in the execution_table for rows marked 'Yes'.

At which step does the join condition fail because not all columns match?

A Step 2

B Step 4

C Step 1

D Step 5

💡 Hint

Look at execution_table row 2 where neither id nor date match.

If the join was changed to 'left' join, what would happen to df1 rows without matches?

A They would be excluded

B They would be duplicated

C They would appear with nulls for df2 columns

D They would cause an error

💡 Hint

In a left join, all df1 rows appear; unmatched df2 columns show nulls.

Concept Snapshot

Multi-column joins combine two tables by matching multiple columns at once.
Syntax: df1.join(df2, on=[col1, col2], how='inner')
Only rows where all join columns match are included in inner join.
Other join types (left, right, outer) handle unmatched rows differently.
Useful to match complex keys like (id, date) pairs.

Full Transcript

Multi-column joins in Apache Spark combine two DataFrames by matching rows where multiple columns have the same values. We specify the columns to join on as a list. The join keeps rows where all these columns match. For example, joining on 'id' and 'date' means only rows with the same id and date in both DataFrames appear in the result. If a row in one DataFrame has no match in the other, it is excluded in an inner join. This process is shown step-by-step in the execution table, where each row from the first DataFrame is checked against rows in the second DataFrame. The variable tracker shows how current rows and result count change during execution. Key moments clarify why some rows are excluded and how all join columns must match. The visual quiz tests understanding of these steps and join behavior. Multi-column joins are useful when a single column is not enough to uniquely match rows.

Practice

1.

What does a multi-column join in Apache Spark do?

Choose the best description.

easy

A. It sorts a DataFrame by multiple columns.

B. It merges two DataFrames by adding columns without any condition.

C. It filters rows from a single DataFrame based on multiple columns.

D. It joins two DataFrames using more than one column to match rows.

Solution

1. Step 1: Understand join basics

   A join combines rows from two tables based on matching column values.

2. Step 2: Recognize multi-column join

   Multi-column join uses more than one column to find matching rows, improving accuracy.

3. Final Answer:

   It joins two DataFrames using more than one column to match rows. -> Option D

4. Quick Check:

   Multi-column join = match on multiple columns [OK]

Hint: Multi-column join matches rows on several columns at once [OK]

Common Mistakes:

• Confusing join with filtering
• Thinking join adds columns without condition
• Mixing join with sorting

2.

Which of the following is the correct syntax to perform a multi-column inner join between df1 and df2 on columns "id" and "date"?

df1.join(df2, _____, how="inner")

easy

A. ["id", "date"]

B. "id, date"

C. df1["id"] == df2["id"] and df1["date"] == df2["date"]

D. on=("id", "date")

Solution

1. Step 1: Check join parameter for columns

   The on parameter accepts a list of column names for multi-column join.

2. Step 2: Identify correct list syntax

   Using ["id", "date"] correctly passes both columns as a list.

3. Final Answer:

   ["id", "date"] -> Option A

4. Quick Check:

   List of columns = ["id", "date"] [OK]

Hint: Use a list of column names for multi-column join [OK]

Common Mistakes:

• Passing columns as a single string
• Using boolean expressions instead of list
• Incorrect tuple syntax for columns

3.

Given the following two DataFrames:

df1 = spark.createDataFrame([
  (1, "2023-01-01", "A"),
  (2, "2023-01-02", "B"),
  (3, "2023-01-03", "C")
], ["id", "date", "value"])

df2 = spark.createDataFrame([
  (1, "2023-01-01", "X"),
  (2, "2023-01-04", "Y"),
  (3, "2023-01-03", "Z")
], ["id", "date", "value2"])

result = df1.join(df2, ["id", "date"], how="inner").select("id", "date", "value", "value2")
result.show()

What will be the output?

medium

A. +---+----------+-----+-------+
|id |date |value|value2 |
+---+----------+-----+-------+
|1 |2023-01-01|A |X |
|2 |2023-01-04|B |Y |
|3 |2023-01-03|C |Z |
+---+----------+-----+-------+

B. +---+----------+-----+-------+
|id |date |value|value2 |
+---+----------+-----+-------+
|2 |2023-01-02|B |Y |
+---+----------+-----+-------+

C. +---+----------+-----+-------+
|id |date |value|value2 |
+---+----------+-----+-------+
|1 |2023-01-01|A |X |
|3 |2023-01-03|C |Z |
+---+----------+-----+-------+

D. +---+----------+-----+-------+
|id |date |value|value2 |
+---+----------+-----+-------+
|1 |2023-01-01|A |null |
|2 |2023-01-02|B |null |
|3 |2023-01-03|C |Z |
+---+----------+-----+-------+

Solution

1. Step 1: Understand inner join on multiple columns

   Inner join keeps rows where both id and date match in both DataFrames.

2. Step 2: Identify matching rows

   Rows with (1, "2023-01-01") and (3, "2023-01-03") match in both DataFrames; (2, "2023-01-02") and (2, "2023-01-04") do not.

3. Final Answer:

   Rows with id 1 and 3 with matching dates and values -> Option C

4. Quick Check:

   Inner join = only matching rows on all columns [OK]

Hint: Inner join keeps only rows matching all join columns [OK]

Common Mistakes:

• Including rows with partial matches
• Confusing inner join with outer join
• Ignoring the multi-column condition

4.

Consider this code snippet:

result = df1.join(df2, on=["id", "date"], how="inner")

When running this, you get an error: TypeError: join() got an unexpected keyword argument 'on'. What is the likely cause?

medium

A. The how parameter must be a list, not a string.

B. The on parameter should be passed as the second positional argument, not as a keyword.

C. The join method does not support multi-column joins.

D. The DataFrames must have the same schema before joining.

Solution

1. Step 1: Check join method signature

   In Spark, join() expects the join columns as the second positional argument, not as a keyword on.

2. Step 2: Correct usage

   Use df1.join(df2, ["id", "date"], how="inner") without on=.

3. Final Answer:

   The on parameter should be passed as the second positional argument, not as a keyword. -> Option B

4. Quick Check:

   Join columns = positional argument [OK]

Hint: Pass join columns as positional argument, not 'on=' keyword [OK]

Common Mistakes:

• Using 'on=' keyword in join
• Passing 'how' as a list
• Assuming join needs identical schemas

5.

You have two DataFrames, orders and customers. You want to join them on customer_id and region, but some customers have missing region values in one DataFrame. You want to keep all orders and add customer info where possible. Which join type and approach should you use?

hard

A. Use a left join on customer_id only, then filter or fill missing region values separately.

B. Use an inner join on both columns; only rows with matching customer_id and region appear.

C. Use a left join on both columns; missing region values will exclude some matches.

D. Use a full outer join on both columns to keep all rows from both DataFrames.

Solution

1. Step 1: Understand join requirements

   You want all orders (left DataFrame) and customer info where available, but region may be missing in one DataFrame.

2. Step 2: Choose join columns carefully

   Joining on both columns will exclude rows with missing region. Joining only on customer_id keeps all orders.

3. Step 3: Handle missing region values separately

   After join, fill or filter missing region values as needed.

4. Final Answer:

   Use a left join on customer_id only, then handle missing region values separately. -> Option A

5. Quick Check:

   Left join on key column + handle missing data [OK]

Hint: Join on reliable keys first, handle missing columns after [OK]

Common Mistakes:

• Joining on columns with missing values directly
• Using inner join and losing rows
• Using full outer join when not needed