Rows That Match, and Rows That Don't

The Daily Grind keeps customers in one table and orders in another, as every system does. Any question that mentions both, who ordered what, who has never ordered, how much each person has spent, needs the two tables put side by side on a key they share. That is a join, and the whole subject is one question asked four ways: which rows do you keep when there is no match?

Watch it happen

Four customers, four orders. Ada has ordered twice, Ben and Dan never, and order 5004 belongs to a customer_id nobody has.

result = customers.join(orders, on="customer_id", how="inner")

Three rows. Ada appears twice, once per order. Ben and Dan are gone, because nothing matched them. Order 5004 is gone, because nothing matched it. on="customer_id" names the shared key, and the result carries it once.

The same join, four answers

how decides what happens to the rows that found no partner.

left keeps every row from the left table and fills the right side with nulls where there was no match. Ben and Dan come back:

customers.join(orders, on="customer_id", how="left")

full keeps unmatched rows from both sides. It is the only one of these four in which order 5004 appears, with a null name, because its customer does not exist.

left_anti keeps the left rows that have no match, and only the left columns. It answers "who has never ordered?" in one line:

customers.join(orders, on="customer_id", how="left_anti")   # Ben, Dan

Its twin left_semi keeps the left rows that do have a match, again with only the left columns: "who has ordered?", without the orders themselves and without Ada twice.

right is left with the tables swapped; swap them yourself and use left.

When the match needs more than one column, a store and a month, on= takes a list, and the result carries each key once:

actuals.join(budgets, on=["store", "month"], how="inner")

Rows multiply

Ada had two orders and came back as two rows. That is correct, and it is also the easiest way to inflate a number by accident: a count after a join counts matches, not customers. Four customers, four orders, three joined rows here, but a join between tables with many matches per key can return far more rows than either side. Before you join, know how many rows per key the right side holds.

Two columns with one name

Join on a condition instead of a shared name and both key columns survive:

customers.join(orders, customers.customer_id == orders.customer_id)

The result has customer_id twice, and the next select("customer_id") fails with ambiguous attribute. The same happens to any column that exists on both sides. Name the key with on= where you can; where you can't, pick the column through its table, customers.customer_id.

Joining a table to itself

The Daily Grind's staff table holds each person's manager as another row's id:

"Who reports to whom" is a join of this table to itself, and the engine cannot tell the two copies apart unless you name them:

e = staff.alias("e")
m = staff.alias("m")

result = (
    e.join(m, e.manager_id == m.id, how="inner")
    .select(e.name.alias("employee"), m.name.alias("manager"))
)

Without the aliases the join fails on ambiguous attribute: manager_id. With them, e.name and m.name are two different columns, and Mo, who has no manager, is dropped by the inner join. Keep Mo with how="left" and a null manager.

Big table, small table

When one side is small, a lookup table or a dimension, a cluster can ship it whole to every executor and skip the shuffle. Spark does that on its own when its estimate of a side is small (under spark.sql.autoBroadcastJoinThreshold, 10 MB by default); F.broadcast(small) is the hint that tells it to broadcast that side whatever its estimate says, which is what you reach for when it has no size for a table, or the wrong one. On this site the plan is identical with or without the hint: Sail, the engine here, already collects the smaller side and looks each row up in it.

Seven problems, in the order they build:

1. Name That Product — an inner join on a shared key.
2. Everyone's Plan — a left join that keeps every user.
3. Who Ordered What — an inner join, columns from both sides.
4. Spend by Name — join, then group.
5. Match on Two Keys — on= takes a list.
6. Ghosted Customers — left_anti for the missing ones.
7. Who Reports to Whom — a table joined to itself, with aliases.