Inner, left, right, and full outer joins in Apache Spark - Deep Dive

Overview - Inner, left, right, and full outer joins

What is it?

Joins are ways to combine two tables or datasets based on matching values in one or more columns. Inner join keeps only the matching rows from both tables. Left join keeps all rows from the left table and matches from the right, filling missing with nulls. Right join keeps all rows from the right table and matches from the left. Full outer join keeps all rows from both tables, filling missing matches with nulls.

Why it matters

Data often comes from different sources and needs to be combined to answer questions or build models. Without joins, you would have to manually match data, which is slow and error-prone. Joins let you merge data efficiently and correctly, enabling better analysis and insights.

Where it fits

Learners should know basic data tables and filtering before joins. After joins, learners can explore aggregations, window functions, and complex data pipelines.

Mental Model

Core Idea

Joins combine two datasets by matching rows on keys, deciding which rows to keep based on the join type.

Think of it like...

Imagine two groups of friends making a guest list for a party. Inner join is the list of friends who appear in both groups. Left join is everyone from the first group plus any matching friends from the second. Right join is everyone from the second group plus matches from the first. Full outer join is everyone from both groups, even if they don’t appear in the other.

┌───────────────┐     ┌───────────────┐
│   Left Table  │     │  Right Table  │
│   (A, B, C)   │     │   (B, C, D)   │
└──────┬────────┘     └──────┬────────┘
       │                     │
       │   Join on common key │
       ▼                     ▼

Inner Join: Rows with keys in both tables (B, C)
Left Join: All rows from Left (A, B, C), matched with Right or null
Right Join: All rows from Right (B, C, D), matched with Left or null
Full Outer Join: All rows from both (A, B, C, D), matched or null

Build-Up - 7 Steps

1

Foundation

Understanding basic table structure

Concept: Learn what tables and columns are and how data is organized.

Tables are like spreadsheets with rows and columns. Each row is a record, and each column holds a type of information. For example, a table of people might have columns for ID, name, and age.

Result

You can identify rows and columns and understand how data is stored in tables.

Understanding tables is essential because joins work by matching rows based on column values.

2

Foundation

What is a join key?

Concept: A join key is the column(s) used to match rows between tables.

When joining two tables, you pick one or more columns that both tables share. These columns act like a bridge to connect related rows. For example, joining on 'user_id' matches rows where user_id is the same in both tables.

Result

You know how to pick columns to connect tables meaningfully.

Choosing the right join key ensures the data merges correctly and answers the right questions.

3

Intermediate

Inner join: matching rows only

🤔Before reading on: do you think inner join keeps unmatched rows or drops them? Commit to your answer.

Concept: Inner join keeps only rows where the join key exists in both tables.

In Apache Spark, inner join is done with df1.join(df2, on='key', how='inner'). It returns rows where the key is in both df1 and df2. Rows without matches in either table are dropped.

Result

A new table with only matching rows from both tables.

Understanding inner join helps you filter data to only the common parts, useful for finding overlaps.

4

Intermediate

Left join: keep all left rows

🤔Before reading on: does left join keep unmatched right rows? Commit to your answer.

Concept: Left join keeps all rows from the left table and matches from the right, filling missing with nulls.

In Spark: df1.join(df2, on='key', how='left'). All rows from df1 appear. If df2 has matching keys, their data is added. If not, nulls fill those columns.

Result

A table with all left rows, enriched with right data where available.

Left join is useful when you want to keep your main dataset intact and add extra info if it exists.

5

Intermediate

Right join: keep all right rows

🤔Before reading on: does right join keep unmatched left rows? Commit to your answer.

Concept: Right join keeps all rows from the right table and matches from the left, filling missing with nulls.

In Spark: df1.join(df2, on='key', how='right'). All rows from df2 appear. If df1 has matching keys, their data is added. If not, nulls fill those columns.

Result

A table with all right rows, enriched with left data where available.

Right join is the mirror of left join, useful when the right table is the main focus.

6

Advanced

Full outer join: keep all rows

🤔Before reading on: does full outer join drop any rows? Commit to your answer.

Concept: Full outer join keeps all rows from both tables, filling missing matches with nulls.

In Spark: df1.join(df2, on='key', how='outer'). Rows from both tables appear. If a row has no match in the other table, nulls fill missing columns.

Result

A combined table with every row from both tables, matched or not.

Full outer join is powerful for combining datasets where you want to keep everything and see where matches exist or not.

7

Expert

Performance and join strategy in Spark

🤔Before reading on: do you think all joins in Spark run equally fast? Commit to your answer.

Concept: Spark uses different join strategies like broadcast join or shuffle join to optimize performance based on data size.

Spark decides how to execute joins. For small tables, it may broadcast one table to all workers for fast joining. For large tables, it shuffles data to group matching keys together. Choosing join type and understanding data size helps optimize speed and resource use.

Result

Efficient join execution that scales to big data without crashing or slowing down.

Knowing Spark’s join strategies helps you write faster code and avoid common performance pitfalls.

Under the Hood

Spark joins work by matching rows on the join key. For large datasets, Spark distributes data across many machines. It either broadcasts the smaller table to all machines or shuffles data so matching keys are co-located. Then it merges rows based on the join type rules, filling missing values with nulls when needed.

Why designed this way?

Spark was built for big data, so joins must work in parallel across clusters. Broadcasting small tables avoids expensive data movement. Shuffling groups keys but costs network time. These tradeoffs balance speed and resource use for different data sizes.

┌───────────────┐       ┌───────────────┐
│   Left Table  │       │  Right Table  │
└──────┬────────┘       └──────┬────────┘
       │                        │
       │ Broadcast small table?  │
       ├─────────────┐          │
       │             ▼          ▼
   ┌─────────┐   ┌───────────────┐
   │Broadcast│   │Shuffle by key │
   └────┬────┘   └──────┬────────┘
        │               │
        ▼               ▼
  ┌─────────────┐  ┌─────────────┐
  │Join matching│  │Join matching│
  │rows on keys │  │rows on keys │
  └─────────────┘  └─────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does inner join keep rows that appear only in one table? Commit yes or no.

Common Belief: Inner join keeps all rows from both tables, even if they don't match.

Reality: Inner join only keeps rows where the join key exists in both tables.

Why it matters: Assuming inner join keeps unmatched rows leads to missing data and wrong analysis.

Quick: Does left join add rows from the right table that don't match? Commit yes or no.

Common Belief: Left join adds all rows from both tables, like a full outer join.

Reality: Left join keeps all rows from the left table only; unmatched right rows are dropped.

Why it matters: Confusing left join with full outer join causes unexpected missing data and errors.

Quick: Is full outer join always slower than inner join? Commit yes or no.

Common Belief: Full outer join is always slower because it keeps more rows.

Reality: Full outer join can be optimized and sometimes runs close to other joins depending on data and Spark's strategy.

Why it matters: Assuming full outer join is always slow may lead to unnecessary workarounds or wrong join choices.

Quick: Does Spark always broadcast the smaller table automatically? Commit yes or no.

Common Belief: Spark always broadcasts the smaller table in joins automatically.

Reality: Spark broadcasts only if the table is below a size threshold and broadcast join is enabled.

Why it matters: Expecting automatic broadcast can cause performance issues if data is large and shuffle happens instead.

Expert Zone

1

Spark's join performance depends heavily on data skew; uneven key distribution can cause slow tasks.

2

Choosing join keys with unique values reduces duplicate matches and speeds up joins.

3

Caching or persisting tables before joins can improve performance if reused multiple times.

When NOT to use

Avoid full outer joins on very large datasets when only matched data is needed; use inner or left joins instead. For very large tables, consider pre-aggregating or filtering before join to reduce data size.

Production Patterns

In production, broadcast joins are used for small lookup tables to speed up joins. Left joins are common when enriching main datasets with optional info. Full outer joins are used in data reconciliation to find mismatches.

Connections

Relational Database SQL Joins

Same pattern implemented in SQL and Spark for combining tables.

Understanding SQL joins helps grasp Spark joins since they follow the same logic and terminology.

Set Theory

Joins correspond to set operations like intersection (inner join) and union (full outer join).

Knowing set theory clarifies why joins behave the way they do and how data overlaps.

Supply Chain Management

Joins are like matching supply orders with deliveries to find fulfilled, missing, or extra items.

Seeing joins as matching real-world lists helps understand their practical use in tracking and reconciliation.

Common Pitfalls

#1 Joining on columns with different names without specifying keys.

Wrong approach:

df1.join(df2, how='inner') # No 'on' specified, columns differ

Correct approach:

df1.join(df2, on=df1['id'] == df2['user_id'], how='inner')

Root cause:

Assuming join works automatically on columns with different names causes no matches or errors.

#2 Using left join when you want only matching rows.

Wrong approach:

df1.join(df2, on='key', how='left') # Keeps unmatched left rows

Correct approach:

df1.join(df2, on='key', how='inner') # Keeps only matched rows

Root cause:

Confusing join types leads to unexpected extra rows and wrong results.

#3 Not handling nulls after outer joins.

Wrong approach:

result_df = df1.join(df2, on='key', how='outer')
# No null handling

Correct approach:

result_df = df1.join(df2, on='key', how='outer').fillna({'column': 'default_value'})

Root cause:

Ignoring nulls from unmatched rows causes errors or misleading analysis downstream.

Key Takeaways

Joins combine datasets by matching rows on keys, with different join types deciding which rows to keep.

Inner join keeps only matching rows; left and right joins keep all rows from one side and matches from the other.

Full outer join keeps all rows from both tables, filling missing matches with nulls.

Choosing the right join type and key is critical for correct and efficient data merging.

Spark optimizes joins using broadcast or shuffle strategies depending on data size and distribution.