Inner, left, right, and full outer joins in Apache Spark - Mini Project: Build & Apply

Inner, left, right, and full outer joins

📖 Scenario: You work at a small online store. You have two lists of data: one with customer names and their IDs, and another with orders made by some customers. You want to learn how to combine these lists in different ways to see all customers, only those who ordered, or all orders with or without customers.

🎯 Goal: Learn how to use inner join, left join, right join, and full outer join in Apache Spark to combine two datasets and understand the differences between these joins.

📋 What You'll Learn

Create two Spark DataFrames with exact data given

Create a variable for join column name

Use inner join, left join, right join, and full outer join on the DataFrames

Print the results of each join

💡 Why This Matters

🌍 Real World

Combining customer and order data is common in business to analyze sales and customer behavior.

💼 Career

Data scientists and analysts often use joins to merge datasets from different sources for reporting and insights.

1

Create the initial DataFrames

Create a Spark DataFrame called customers with these exact rows: (1, 'Alice'), (2, 'Bob'), (3, 'Charlie'). Also create a Spark DataFrame called orders with these exact rows: (1, 'Book'), (2, 'Pen'), (4, 'Notebook'). Use column names 'customer_id' and 'name' for customers, and 'customer_id' and 'product' for orders. Assume SparkSession is available as spark.

Apache Spark

# Create the customers DataFrame
# Create the orders DataFrame

Hint

Use spark.createDataFrame with a list of tuples and a list of column names.

2

Set the join column

Create a variable called join_column and set it to the string 'customer_id'. This will be used to join the DataFrames.

Apache Spark

customers = spark.createDataFrame([
    (1, 'Alice'),
    (2, 'Bob'),
    (3, 'Charlie')
], ['customer_id', 'name'])

orders = spark.createDataFrame([
    (1, 'Book'),
    (2, 'Pen'),
    (4, 'Notebook')
], ['customer_id', 'product'])

# Create the join_column variable
# Your code here

Hint

Just assign the string 'customer_id' to the variable join_column.

3

Perform the joins

Use the join method on customers and orders with join_column to create four DataFrames: inner_join with how='inner', left_join with how='left', right_join with how='right', and full_outer_join with how='outer'.

Apache Spark

customers = spark.createDataFrame([
    (1, 'Alice'),
    (2, 'Bob'),
    (3, 'Charlie')
], ['customer_id', 'name'])

orders = spark.createDataFrame([
    (1, 'Book'),
    (2, 'Pen'),
    (4, 'Notebook')
], ['customer_id', 'product'])

join_column = 'customer_id'

# Create inner_join, left_join, right_join, and full_outer_join DataFrames
# Your code here

Hint

Use customers.join(orders, join_column, how='inner') and similarly for other join types.

4

Show the join results

Use the show() method to print the results of inner_join, left_join, right_join, and full_outer_join DataFrames in this order.

Apache Spark

customers = spark.createDataFrame([
    (1, 'Alice'),
    (2, 'Bob'),
    (3, 'Charlie')
], ['customer_id', 'name'])

orders = spark.createDataFrame([
    (1, 'Book'),
    (2, 'Pen'),
    (4, 'Notebook')
], ['customer_id', 'product'])

join_column = 'customer_id'

inner_join = customers.join(orders, join_column, how='inner')
left_join = customers.join(orders, join_column, how='left')
right_join = customers.join(orders, join_column, how='right')
full_outer_join = customers.join(orders, join_column, how='outer')

# Show the results of each join
# Your code here

Hint

Call show() on each join DataFrame in order: inner_join, left_join, right_join, full_outer_join.

Practice

1. Which type of join in Apache Spark returns only the rows that have matching keys in both DataFrames?

easy

A. Full outer join

B. Inner join

C. Right outer join

D. Left outer join

Solution

1. Step 1: Understand join types

   Inner join returns rows where keys match in both tables only.

2. Step 2: Compare with other joins

   Left, right, and full outer joins include unmatched rows from one or both tables.

3. Final Answer:

   Inner join -> Option B

4. Quick Check:

   Inner join = matching rows only [OK]

Hint: Inner join keeps only matching rows from both tables [OK]

Common Mistakes:

• Confusing inner join with left or right join
• Thinking full outer join returns only matches
• Assuming left join excludes unmatched rows

2. Which of the following is the correct syntax to perform a left outer join between two DataFrames df1 and df2 on column id in Apache Spark?

easy

A. df1.join(df2, df1.id == df2.id, 'inner')

B. df1.join(df2, df1.id == df2.id, 'full')

C. df1.join(df2, df1.id == df2.id, 'right_outer')

D. df1.join(df2, df1.id == df2.id, 'left')

Solution

1. Step 1: Identify join type keyword

   Left outer join uses 'left' as the join type string in Spark.

2. Step 2: Match syntax format

   The syntax is df1.join(df2, condition, 'left').

3. Final Answer:

   df1.join(df2, df1.id == df2.id, 'left') -> Option D

4. Quick Check:

   Left join keyword = 'left' [OK]

Hint: Use 'left' string for left outer join in join() method [OK]

Common Mistakes:

• Using 'left_outer' instead of 'left'
• Confusing join type strings like 'right_outer' for left join
• Omitting join condition or using wrong syntax

3. Given two DataFrames:
df1 = spark.createDataFrame([(1, 'A'), (2, 'B')], ['id', 'val1'])
df2 = spark.createDataFrame([(2, 'X'), (3, 'Y')], ['id', 'val2'])
What will be the result of df1.join(df2, 'id', 'right')?

medium

A. [ (1, 'A', null), (2, 'B', 'X'), (3, null, 'Y') ]

B. [ (1, 'A', null), (2, 'B', 'X') ]

C. [ (2, 'B', 'X'), (3, null, 'Y') ]

D. [ (2, 'B', 'X') ]

Solution

1. Step 1: Understand right join behavior

   Right join keeps all rows from right DataFrame (df2) and matches from left (df1).

2. Step 2: Match rows by 'id'

   Id=2 matches in both, id=3 only in df2, so df1 columns are null for id=3.

3. Final Answer:

   [ (1, 'A', null), (2, 'B', 'X'), (3, null, 'Y') ] -> Option A

4. Quick Check:

   Right join keeps all right rows [OK]

Hint: Right join keeps all right table rows, fills left with nulls if no match [OK]

Common Mistakes:

• Expecting left table rows to appear in right join
• Confusing right join with full outer join
• Misunderstanding null placement for unmatched rows

4. You wrote this Spark join code:
df1.join(df2, df1.id == df2.id, 'full_outerjoin')
But it throws an error. What is the likely cause?

medium

A. The join type string should be 'full' instead of 'full_outerjoin'

B. The join condition must use a string column name, not an expression

C. You must use 'outer' instead of 'full_outerjoin' as join type

D. Spark does not support full outer joins

Solution

1. Step 1: Check valid join type strings in Spark

   Spark expects 'full' or 'fullouter' but not 'full_outerjoin'.

2. Step 2: Identify correct string for full outer join

   The correct string is 'full', so 'full_outerjoin' causes error.

3. Final Answer:

   The join type string should be 'full' instead of 'full_outerjoin' -> Option A

4. Quick Check:

   Use 'full' for full outer join [OK]

Hint: Use 'full' not 'full_outerjoin' for full outer join string [OK]

Common Mistakes:

• Using 'full_outerjoin' instead of 'full'
• Thinking join condition must be string only
• Believing Spark lacks full outer join support

5. You have two DataFrames:
df1 = spark.createDataFrame([(1, 'A'), (2, 'B'), (4, 'D')], ['id', 'val1'])
df2 = spark.createDataFrame([(2, 'X'), (3, 'Y'), (4, 'Z')], ['id', 'val2'])
You want a DataFrame that shows all ids from both tables, with values from both, and null where no match exists. Which join should you use and what will be the output?

hard

A. Use left join; output: [(1, 'A', null), (2, 'B', 'X'), (4, 'D', 'Z')]

B. Use inner join; output: [(2, 'B', 'X'), (4, 'D', 'Z')]

C. Use full outer join; output: [(1, 'A', null), (2, 'B', 'X'), (3, null, 'Y'), (4, 'D', 'Z')]

D. Use right join; output: [(2, 'B', 'X'), (3, null, 'Y'), (4, 'D', 'Z')]

Solution

1. Step 1: Identify join type for all rows from both tables

   Full outer join returns all rows from both DataFrames, filling nulls where no match.

2. Step 2: Match rows and fill nulls

   Ids 1 and 3 appear only in one table, so their unmatched columns are null.

3. Final Answer:

   Use full outer join; output: [(1, 'A', null), (2, 'B', 'X'), (3, null, 'Y'), (4, 'D', 'Z')] -> Option C

4. Quick Check:

   Full outer join = all rows, nulls for no match [OK]

Hint: Full outer join keeps all rows from both tables with nulls [OK]

Common Mistakes:

• Using inner join and missing unmatched rows
• Confusing left or right join with full outer join
• Expecting no nulls in full outer join result