Inner, left, right, and full outer joins in Apache Spark - Step-by-Step Execution

Concept Flow - Inner, left, right, and full outer joins

Start with two tables

↓

Choose join type

↓

Inner

↓

Match rows based on key

↓

Return joined table with different rules

↓

End

Start with two tables, pick a join type, match rows by key, and return a combined table based on join rules.

Execution Sample

Apache Spark

df1 = spark.createDataFrame([(1, 'A'), (2, 'B')], ['id', 'val1'])
df2 = spark.createDataFrame([(2, 'X'), (3, 'Y')], ['id', 'val2'])
inner = df1.join(df2, 'id', 'inner')
left = df1.join(df2, 'id', 'left')
right = df1.join(df2, 'id', 'right')
full = df1.join(df2, 'id', 'outer')

Create two small tables and join them using inner, left, right, and full outer joins.

Execution Table

Join Type	Rows Matched	Rows Returned	Explanation
Inner	id=2	1 row	Only rows with matching id in both tables are returned.
Left	id=1,2	2 rows	All rows from left table; matched rows from right or null.
Right	id=2,3	2 rows	All rows from right table; matched rows from left or null.
Full Outer	id=1,2,3	3 rows	All rows from both tables; unmatched sides get null.

💡 All join types complete after processing all keys from both tables.

Variable Tracker

Variable	Start	After Inner Join	After Left Join	After Right Join	After Full Outer Join
inner	empty	[{2, B, X}]	[{2, B, X}]	[{2, B, X}]	[{2, B, X}]
left	empty	empty	[{1, A, null}, {2, B, X}]	empty	empty
right	empty	empty	empty	[{2, B, X}, {3, null, Y}]	empty
full	empty	empty	empty	empty	[{1, A, null}, {2, B, X}, {3, null, Y}]

Key Moments - 3 Insights

Why does the inner join return fewer rows than the full outer join?

Inner join returns only rows with matching keys in both tables (see execution_table row 1), while full outer join returns all rows from both tables, filling unmatched sides with null (row 4).

Why does the left join include a row with null values?

Left join keeps all rows from the left table even if no match in right table, so unmatched right columns are null (execution_table row 2).

What happens to rows in the right table that have no match in the left during a right join?

They appear in the result with nulls for left table columns (execution_table row 3).

Visual Quiz - 3 Questions

Look at the execution_table, how many rows does the left join return?

A 1 row

B 2 rows

C 3 rows

D 0 rows

💡 Hint

Check the 'Rows Returned' column for the Left join in the execution_table.

At which join type do unmatched keys from both tables appear with nulls on the opposite side?

A Full outer join

B Left join

C Inner join

D Right join

💡 Hint

Look at the Explanation for Full Outer join in the execution_table.

If the key 'id=3' was removed from df2, which join would return fewer rows?

A Inner join

B Left join

C Right join

D Full outer join

💡 Hint

Check which join depends on keys only in the right table (see variable_tracker for right join).

Concept Snapshot

Joins combine two tables by matching keys.
Inner join: only matching rows.
Left join: all left rows + matched right.
Right join: all right rows + matched left.
Full outer join: all rows from both, unmatched get nulls.

Full Transcript

We start with two tables and want to combine them based on a shared key. We pick a join type: inner, left, right, or full outer. Inner join returns only rows where keys match in both tables. Left join returns all rows from the left table, adding matching rows from the right or null if no match. Right join returns all rows from the right table, adding matching rows from the left or null if no match. Full outer join returns all rows from both tables, filling unmatched sides with nulls. We saw example data and how each join returns different rows. This helps us combine data flexibly depending on what we want to keep.

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