Multi-column joins in Apache Spark - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of multi-column join with inner join type

Given two Spark DataFrames df1 and df2 joined on columns id and category using inner join, what is the output of the following code?

Apache Spark

from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()
data1 = [(1, 'A', 100), (2, 'B', 200), (3, 'A', 300)]
data2 = [(1, 'A', 'X'), (2, 'B', 'Y'), (2, 'C', 'Z')]
df1 = spark.createDataFrame(data1, ['id', 'category', 'value1'])
df2 = spark.createDataFrame(data2, ['id', 'category', 'value2'])
joined_df = df1.join(df2, on=['id', 'category'], how='inner')
result = joined_df.collect()

A [Row(id=1, category='A', value1=100, value2='X'), Row(id=2, category='B', value1=200, value2='Y'), Row(id=2, category='C', value1=None, value2='Z')]

B [Row(id=1, category='A', value1=100, value2='X'), Row(id=2, category='B', value1=200, value2='Y')]

C [Row(id=1, category='A', value1=100, value2='X'), Row(id=3, category='A', value1=300, value2=None)]

D [Row(id=2, category='C', value1=None, value2='Z')]

💡 Hint

Inner join returns only rows with matching keys in both DataFrames.

An inner join on columns 'id' and 'category' returns rows where both DataFrames have the same values in these columns. Rows with id=2 and category='C' are only in df2, so they are excluded.

❓ data_output

intermediate

Number of rows after left join on multiple columns

If you perform a left join of df1 with df2 on columns id and category, how many rows will the resulting DataFrame have?

Apache Spark

from pyspark.sql import SparkSession
spark = SparkSession.builder.getOrCreate()
data1 = [(1, 'A', 100), (2, 'B', 200), (3, 'A', 300)]
data2 = [(1, 'A', 'X'), (2, 'B', 'Y'), (2, 'C', 'Z')]
df1 = spark.createDataFrame(data1, ['id', 'category', 'value1'])
df2 = spark.createDataFrame(data2, ['id', 'category', 'value2'])
joined_df = df1.join(df2, on=['id', 'category'], how='left')
count = joined_df.count()

A 2

B 4

C 3

D 5

💡 Hint

Left join keeps all rows from the left DataFrame.

Left join keeps all rows from df1 (3 rows). It adds matching rows from df2 or nulls if no match. So total rows remain 3.

🔧 Debug

advanced

Identify the error in multi-column join code

What error will this Spark code raise when trying to join two DataFrames on multiple columns?

Apache Spark

df1.join(df2, on='id,category', how='inner')

A AnalysisException: cannot resolve 'id,category' given input columns

B SyntaxError: invalid syntax

C No error, join works correctly

D TypeError: join() argument 'on' must be a string or a list of strings

💡 Hint

The 'on' parameter expects a list of column names for multiple columns.

Passing a single string with comma-separated column names is not valid. Spark treats it as one column name, which does not exist, causing AnalysisException.

🚀 Application

advanced

Choosing join type for multi-column join scenario

You have two DataFrames: sales with columns store_id, date, sales_amount and targets with columns store_id, date, target_amount. You want to keep all sales records and add target amounts where available. Which join type should you use on columns store_id and date?

A Full outer join

B Inner join

C Right join

D Left join

💡 Hint

You want to keep all sales records even if no target exists.

Left join keeps all rows from the left DataFrame (sales) and adds matching target data or null if no match.

🧠 Conceptual

expert

Effect of join keys order in multi-column join

In Spark DataFrame join, does the order of columns in the on list (e.g., ['id', 'category'] vs ['category', 'id']) affect the join result?

A No, the order of join keys does not affect the join result

B No, but it affects the performance of the join

C Yes, the order changes the join keys and result rows

D Yes, but only for outer joins, not inner joins

💡 Hint

Join keys are matched as a set, order does not matter for equality.

Spark matches rows where all join keys are equal regardless of order in the list. The join result is the same.