Relational Joins with pd.merge (Inner, Left, Right, Outer)0%
Reshaping Data: Melt, Stack & Unstack

Relational Joins with pd.merge (Inner, Left, Right, Outer)

Beginner12 min read•Updated: Jul 11, 2026
Study MaterialsCheatsheet

Relational Joins with pd.merge (Inner, Left, Right, Outer)

Reading Time: 8 Mins | Level: Beginner to Advanced

Pandas Merge JoinsClick to Zoom
Pandas Merge Joins

1. What is Relational Merging?

While concat() simply glues tables together, pd.merge() combines DataFrames based on matching values in common key columns—identically to an SQL JOIN or Excel XLOOKUP / VLOOKUP.

2. The 4 Relational Join Types

1. Inner Join (how='inner' - Default)

Keeps only the rows where the join key exists in both tables (the intersection):

Python
import pandas as pd
 
df_customers = pd.DataFrame({
'Cust_ID': [1, 2, 3, 4],
'Name': ['Amit', 'Priya', 'Rohan', 'Sneha']
})
 
df_orders = pd.DataFrame({
'Cust_ID': [2, 3, 5],
'Order_Amount': [4500, 7200, 1900]
})
 
# Inner join: only customers 2 and 3 match!
inner_df = pd.merge(df_customers, df_orders, on='Cust_ID', how='inner')
print(inner_df)

2. Left Join (how='left')

Keeps all rows from the left table. If no match exists in the right table, right-side columns are filled with NaN:

Python
# Keeps all customers (Amit, Priya, Rohan, Sneha)
left_df = pd.merge(df_customers, df_orders, on='Cust_ID', how='left')

3. Right Join (how='right')

Keeps all rows from the right table. Customer 5 (who is not in the customer master table) is included:

Python
right_df = pd.merge(df_customers, df_orders, on='Cust_ID', how='right')

4. Full Outer Join (how='outer')

Keeps all rows from both tables, unioning unmatched records with NaN:

Python
outer_df = pd.merge(df_customers, df_orders, on='Cust_ID', how='outer')

3. Merging on Differently Named Columns

When the left table calls the key client_id and the right table calls it cust_number:

Python
merged_df = pd.merge(
df_left,
df_right,
left_on='client_id',
right_on='cust_number',
how='left'
)

4. Detecting Unmatched Records with indicator=True

To audit which table contributed each record (essential for data reconciliation):

Python
audit_df = pd.merge(df_customers, df_orders, on='Cust_ID', how='outer', indicator=True)
print(audit_df['_merge'].value_counts())

Output:

Output
both 2
left_only 2
right_only 1
Name: _merge, dtype: int64
Interactive Quiz

Multiple Choice Questions

Question 1
What is the default join type used in pd.merge(df1, df2, on='key')?
Question 2
When joining two DataFrames where the key column is named 'emp_id' in the left table and 'employee_number' in the right table, which parameters must be used?
Question 3
What does setting indicator=True add to the merged DataFrame?
Question 4
Which join type preserves every single record from the left DataFrame regardless of whether a matching record exists in the right table?
Question 5
What parameter resolves overlapping column names (e.g., both tables have a 'Date' column) during a merge?
Next Lesson

Reshaping Data: Melt, Stack & Unstack

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous LessonNext Lesson
Concatenating DataFrames (pd.concat)Reshaping Data: Melt, Stack & Unstack

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

PreviousNext
PrevNext