Inner, Left, Right and Outer Joins

A join has to decide what happens to rows that find no match, and merge's how= says which ones to keep (inner only the matches, left every row of the left table, right every row of the right one, outer every row of both), while indicator=True and a row count before and after show what the join did.

Journeyman · Python · Database · Updated

By

After this lesson you can

  • Choose inner, left, right or outer with how= by saying which rows that don't match each one keeps
  • Find the rows a join didn't match with indicator=True, and check the row counts before and after a join

Key takeaways

  • merge keeps only the rows that match unless you say otherwise; how="inner" is the default, and the rows that found no match disappear without a word.
  • how="left" keeps every row of the left table, how="right" every row of the right one, and how="outer" every row of both, with NaN where nothing matched.
  • indicator=True adds a _merge column that says both, left_only or right_only for every row, so you can count and look at the rows that didn't match.
  • Count the rows before and after every join. A count that changes when you didn't expect it is the first sign of a problem.

Real tables never match perfectly. A customer with no orders, an order for a product someone deleted, a club nobody joined yet: every join has to decide what to do with rows like these, and the choice changes your answer. You'll make this choice every time you join, so it's worth making on purpose.

The example is Oakridge Middle School's after-school clubs: clubs.csv (8 clubs: club_id, club_name, room) and memberships.csv (student_id, club_id: one row for each student in each club, as the club advisers typed them).

A row disappears

The activities coordinator wants every membership with its club's name. Joining Tables with merge does that:

import pandas as pd

clubs = pd.read_csv("clubs.csv")
memberships = pd.read_csv("memberships.csv")
inner = memberships.merge(clubs, on="club_id")
print("memberships:", len(memberships))
print("rows after:", len(inner))
memberships: 237
rows after: 236

One membership is gone, and pandas said nothing. Unless you tell it otherwise, merge keeps only the rows whose key is in both tables. That's called an inner join, and the count before and after is the only thing that gave it away.

how=: which rows to keep

Picture two overlapping circles: the left one holds every row of the left table, memberships, and the right one every row of clubs. The overlap is the rows that match. how= says which parts to keep:

  • how="inner" (the default): only the overlap, the rows that match.
  • how="left": the whole left circle. Every membership stays, and a membership whose club isn't in clubs gets NaN (missing) for club_name and room.
  • how="right": the whole right circle. Every club stays, with NaN where no membership matched.
  • how="outer": both circles. Every row of both tables.

Four pairs of overlapping circles, one for each join, with memberships on the left and clubs on the right: inner keeps only the overlap and gives 236 rows, left keeps the whole memberships circle and gives 237, right keeps the whole clubs circle and gives 237, and outer keeps both circles and gives 238

for how in ["inner", "left", "right", "outer"]:
    joined = memberships.merge(clubs, on="club_id", how=how)
    print(how, len(joined))
inner 236
left 237
right 237
outer 238

Four joins, four answers. If you know SQL, these are INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL OUTER JOIN, and they keep the same rows. A right join is a left join with the tables swapped, so most people write left joins and put the table they want to keep whole on the left.

indicator=True: which rows matched

The counts say how many rows didn't match, not which. indicator=True adds a column, _merge, that says where each row came from: both, left_only or right_only. An outer join keeps every row, so it can show them all:

checked = memberships.merge(clubs, on="club_id", how="outer", indicator=True)
checked["_merge"].value_counts()
_merge
both          236
left_only       1
right_only      1
Name: count, dtype: int64
checked[checked["_merge"] != "both"]
     student_id club_id club_name     room      _merge
236         NaN     C08  Yearbook  Library  right_only
237      1037.0     CO3       NaN      NaN   left_only

Two stories. Yearbook (C08) is new this year and has no members yet, so nothing from memberships matched it. And an adviser typed CO3, with the letter O, for Art Club's C03, with a zero: student 1037's membership points at a club that doesn't exist. (The student_id column shows decimals now, because a missing value turns a column of whole numbers into decimals.) The inner join hid both; the indicator found them.

Choosing the join

Start from the question, and decide which table's rows must all be in the answer:

  • "Every membership, with its club's name" keeps every membership: a left join from memberships. It gives 237 rows, and one club_name is missing, which is your cue to fix CO3.
  • "Members of each club, counting only memberships we can place" uses only the matches: an inner join.
  • "Anything that doesn't line up, on either side" needs both: an outer join with indicator=True.

Whichever you choose, write down the row count before and after. A left join that keeps every row of the left table should end with at least as many rows as it started with; When a Join Goes Wrong shows what it means when it ends with more.

Check yourself

2 questions to try on the lesson page.

So what

Every join quietly decides what to do with the rows that don't match; how= makes that decision yours, and indicator=True with a count before and after shows you exactly what it cost.

Key terms

Related