Join Types: inner, left, right, outer, cross

The how argument answers one question: "What should happen to rows that have no partner in the other table?" We keep using the same two tables — Pant is only in bat, Bumrah is only in info:

 bat (LEFT)            info (RIGHT)
 player  runs          player  team
 Kohli   741           Kohli   RCB
 Rohit   417           Rohit   MI
 Gill    890           Gill    GT
 Pant    446  <- only  Bumrah  MI   <- only right
                 left

Definition

The join type (how=) decides which keys appear in the merge result: inner keeps keys found in both tables, left keeps all keys of the left table, right keeps all keys of the right table, outer keeps keys from either table, and cross ignores keys and pairs every left row with every right row. Columns from the side without a match are filled with NaN.

Venn-style summary

        LEFT keys        RIGHT keys
      ┌──────────┬───┬──────────┐
      │  Pant    │ K │  Bumrah  │      K = Kohli, Rohit, Gill (in both)
      │ (left    │ R │ (right   │
      │  only)   │ G │  only)   │
      └──────────┴───┴──────────┘
  inner = middle only            left  = left circle (left only + middle)
  outer = everything             right = right circle (middle + right only)

Syntax

left.merge(right, on="key", how="inner" | "left" | "right" | "outer")
left.merge(right, how="cross")                    # no on= allowed
left.merge(right, on="key", how="outer", indicator=True)

Example — inner (default)

print(bat.merge(info, on="player", how="inner"))

  player  runs team
0  Kohli   741  RCB
1  Rohit   417   MI
2   Gill   890   GT

Only the overlap. Use it when a row is useless without data from both sides.

Example — left

print(bat.merge(info, on="player", how="left"))

  player  runs team
0  Kohli   741  RCB
1  Rohit   417   MI
2   Gill   890   GT
3   Pant   446  NaN

 LEFT row    ->  partner in RIGHT?  ->  result
 Kohli 741       Kohli RCB  yes         Kohli 741 RCB
 Rohit 417       Rohit MI   yes         Rohit 417 MI
 Gill  890       Gill  GT   yes         Gill  890 GT
 Pant  446       -          no          Pant  446 NaN   (kept, team = NaN)
 (Bumrah is only in RIGHT -> dropped)

How it works: every left row is kept in its original order; a missing partner gives NaN. This is the most-used join in practice: "keep my main table, look up extra info where available." If the right keys are unique, the result has exactly len(left) rows.

Example — right

print(bat.merge(info, on="player", how="right"))

   player   runs team
0   Kohli  741.0  RCB
1   Rohit  417.0   MI
2    Gill  890.0   GT
3  Bumrah    NaN   MI

The mirror image: all of info is kept, Bumrah has no runs → NaN (so runs turns to float). a.merge(b, how="right") gives the same rows as b.merge(a, how="left") (only the column order differs).

Example — outer with indicator=True

print(bat.merge(info, on="player", how="outer", indicator=True))

   player   runs team      _merge
0  Bumrah    NaN   MI  right_only
1    Gill  890.0   GT        both
2   Kohli  741.0  RCB        both
3    Pant  446.0  NaN   left_only
4   Rohit  417.0   MI        both

How it works: outer keeps everyone; NaN can appear on either side. Notice the outer result is sorted by key (alphabetical). indicator=True labels each row's origin — perfect for data quality checks: result["_merge"].value_counts() tells you how many rows matched.

A neat trick — the anti-join ("players with runs but no team info"):

o = bat.merge(info, on="player", how="left", indicator=True)
print(o[o["_merge"] == "left_only"].drop(columns="_merge"))

  player  runs team
3   Pant   446  NaN

Example — cross: every combination

teams = pd.DataFrame({"team": ["MI", "CSK", "RCB"]})
venues = pd.DataFrame({"venue": ["Home", "Away"]})
print(teams.merge(venues, how="cross"))

  team venue
0   MI  Home
1   MI  Away
2  CSK  Home
3  CSK  Away
4  RCB  Home
5  RCB  Away

How it works: a Cartesian product — 3 × 2 = 6 rows, no key. Useful for generating fixtures or "all combinations" grids. Cross-joining a table with itself (teams.merge(teams, how="cross", suffixes=("_home", "_away"))) and removing rows where both teams are equal gives every home/away fixture. Careful: 1,000 × 1,000 rows = 1,000,000 rows.

Common mistakes

• ❌ Using the default (inner) and silently losing unmatched rows. ✅ Decide consciously; check with indicator=True.
• ❌ Expecting a left join to keep the right table's extra rows. ✅ That is right or outer.
• ❌ Being surprised that integer columns became floats. ✅ Unmatched rows bring NaN (a float); fillna / astype("Int64") afterwards.
• ❌ Passing on= with how="cross" → MergeError. ✅ Cross joins take no keys.
• ❌ Assuming pandas treats missing keys like SQL. ✅ In pandas, NaN keys do match each other (SQL's NULL never matches) — drop or fill missing keys first.

Interview tip

Q: You merged a 1,000-row orders table with a customers table using how="left" and got 1,050 rows. Why? A: Some keys appear more than once in the right table, so those left rows matched several right rows and were duplicated. Check right[key].duplicated().sum() or use validate="many_to_one" (next topic).