20. Left, right, and full outer joins

An outer join keeps rows that don't match, filling the missing side with nulls. Which side you preserve is the whole game.

Left join — keep every left row

how="left" keeps all rows of the left DataFrame; unmatched right columns become null. Our orphan order (customer c019, not in customers) now survives — with null customer fields:

orders.dropDuplicates().join(customers, "customer_id", "left").filter(
    F.col("name").isNull()
).select("order_id", "customer_id", "name").show()

That's the classic use of a left join: find the rows that didn't match by filtering for nulls on the right side. Order 1037 points at a customer who doesn't exist.

Right join — keep every right row

how="right" is the mirror image: every customers row survives, even those with no orders (c021, c022):

customers.join(orders.dropDuplicates(), "customer_id", "right").select(
    "customer_id", "name", "order_id"
).count()

a.join(b, key, "right") is the same as b.join(a, key, "left") — most people just always use left and swap the sides.

Full outer — keep everything

how="full" (or "outer") keeps all rows from both sides, matching where it can and nulling where it can't — orphan orders and order-less customers all appear:

orders.dropDuplicates().join(customers, "customer_id", "full").select(
    "order_id", "customer_id", "name"
).count()

Your turn

Left-join deduplicated orders to customers, then keep only the orders whose customer is unknown (customer name is null). Return order_id and customer_id. Assign to result.

result = orders  # <- dropDuplicates, left join customers, keep name IS NULL, select order_id & customer_id
result.show()

result = (
    orders.dropDuplicates()
    .join(customers, "customer_id", "left")
    .filter(F.col("name").isNull())
    .select("order_id", "customer_id")
)