merge() for SQL-like joins in Pandas

Introduction

We use merge() to combine two tables based on common columns, just like joining tables in a database. It helps us see related data together.

You have two lists of customer info and their orders, and want to see all orders with customer details.

You want to combine sales data from two different months into one table based on product IDs.

You have employee info and their department info in separate tables and want to see full employee details with department names.

You want to find customers who made purchases in both January and February by joining two purchase tables.

Syntax

Pandas

pd.merge(left, right, how='inner', on=None, left_on=None, right_on=None)

left and right are the two tables (DataFrames) to join.

how decides the type of join: 'inner', 'left', 'right', or 'outer'.

Examples

Join two tables on the column 'id' using an inner join (default).

Pandas

pd.merge(df1, df2, on='id')

Keep all rows from df1 and add matching rows from df2.

Pandas

pd.merge(df1, df2, how='left', on='id')

Keep all rows from both tables, filling missing matches with NaN.

Pandas

pd.merge(df1, df2, how='outer', on='id')

Join using different column names in each table.

Pandas

pd.merge(df1, df2, left_on='user_id', right_on='id')

Sample Program

This code shows how to join two tables of users and orders using different join types. It prints the results so you can see how rows match or stay when no match is found.

Pandas

import pandas as pd

# Create first table with user info
df_users = pd.DataFrame({
    'user_id': [1, 2, 3],
    'name': ['Alice', 'Bob', 'Charlie']
})

# Create second table with orders
df_orders = pd.DataFrame({
    'order_id': [101, 102, 103, 104],
    'user_id': [2, 2, 3, 4],
    'product': ['Book', 'Pen', 'Notebook', 'Pencil']
})

# Merge tables to see orders with user names
merged_inner = pd.merge(df_users, df_orders, on='user_id')
merged_left = pd.merge(df_users, df_orders, how='left', on='user_id')
merged_outer = pd.merge(df_users, df_orders, how='outer', on='user_id')

print('Inner Join Result:')
print(merged_inner)
print('\nLeft Join Result:')
print(merged_left)
print('\nOuter Join Result:')
print(merged_outer)

Important Notes

If columns have different names, use left_on and right_on to specify them.

Inner join keeps only matching rows; left join keeps all from left table; right join keeps all from right table; outer join keeps all rows from both.

Missing matches fill with NaN, which means 'no data'.

Summary

merge() combines two tables based on matching columns.

You can choose how to join: inner, left, right, or outer.

It helps you see related data from different sources together easily.