19. Inner join

Joins combine rows from two DataFrames on a matching key. Our orders have a customer_id; customers describe each one. Join them to put names next to orders.

orders.dropDuplicates().join(customers, "customer_id").select(
    "order_id", "product", "name", "city"
).show(5)

Passing the key as a string ("customer_id") when both sides share the column name is the cleanest form — the key appears once in the result, no duplicate column.

Inner means "matches on both sides"

An inner join (the default) keeps only rows that match in both DataFrames. Watch the count: one order references customer c019, who isn't in customers — the inner join silently drops it.

o = orders.dropDuplicates()
print("orders:", o.count())
print("inner-joined:", o.join(customers, "customer_id").count())  # one fewer — the orphan

That "silently drops non-matches" behavior is the thing to stay alert to. The next lesson (outer joins) is exactly about keeping those rows so you can see what didn't match.

Joining on multiple keys / different names

Multiple keys: pass a list — join(other, ["a", "b"]). When the columns are named differently, use a condition instead:

orders.dropDuplicates().join(
    customers, orders["customer_id"] == customers["customer_id"]
).select("order_id", "name").show(3)

(With a condition both customer_id columns survive — the string form is nicer when the names match.)

Your turn

Inner-join deduplicated orders to customers and return order_id, product, the customer's name, and city. Assign to result.

result = orders  # <- dropDuplicates, join customers on "customer_id", select the 4 columns
result.show()

result = orders.dropDuplicates().join(customers, "customer_id").select(
    "order_id", "product", "name", "city"
)