This lesson's interactive features are locked, please to keep using them
Outer Joins
Outer joins are less common than inner and left, but they can be useful to solve specific problems. As for "right" joins... they're dumb. A right join is just a left join with the DataFrames swapped. Don't use them.
An "outer" join keeps all rows from both DataFrames, filling in NaN where data is missing from either side.
sales = pd.DataFrame(
{"product_id": ["CHL-482", "TST-731", "OVN-205"], "quantity": [10, 20, 30]}
)
products = pd.DataFrame(
{
"product_id": ["CHL-482", "TST-731", "BRW-619"],
"name": ["ChillVault Mini", "ToastForge Pro", "BrewPilot"],
}
)
result = pd.merge(sales, products, on="product_id", how="outer")
print(result)
# product_id quantity name
# 0 BRW-619 NaN BrewPilot
# 1 CHL-482 10.0 ChillVault Mini
# 2 OVN-205 30.0 NaN
# 3 TST-731 20.0 ToastForge Pro
With this join, everything survives the merge. Product OVN-205 has a name of NaN, and product BRW-619 has a quantity of NaN.
Use outer joins when: you want to see everything, including gaps. This is especially useful for audits and data quality checks.
Comparing Join Results
Count rows to understand your data overlap:
inner_count = len(pd.merge(sales, products, on="product_id", how="inner"))
left_count = len(pd.merge(sales, products, on="product_id", how="left"))
outer_count = len(pd.merge(sales, products, on="product_id", how="outer"))
print(f"Inner: {inner_count}") # 2 (only matches)
print(f"Left: {left_count}") # 3 (all sales)
print(f"Outer: {outer_count}") # 4 (everything)
In a larger dataset, if inner_count were much smaller than left_count, you'd probably have a lot of sales with invalid product IDs. That's a data-quality problem worth investigating.
Assignment
SnackStack's analytics team wants a quick overlap report for product sales: how many rows survive each type of join?
Complete the count_join_rows function. It accepts two DataFrames: sales and products.