21. Semi and anti joins — filtering with a join

Sometimes you don't want to combine two tables — you want to use one to filter the other. That's what semi and anti joins do. They return columns from the left side only; the right side is used purely as a membership test.

Left semi — "keep left rows that have a match"

# customers who have placed at least one order
customers.join(orders, "customer_id", "left_semi").select(
    "customer_id", "name"
).count()

A semi join is like filter(exists in other) — but it never duplicates left rows the way an inner join would, and it returns no columns from the right. It answers "which of these have a match?"

Left anti — "keep left rows that have NO match"

The opposite, and incredibly handy for finding gaps:

# customers who have never ordered — c021 and c022
customers.join(orders, "customer_id", "left_anti").select(
    "customer_id", "name", "country"
).show()

Point it the other way to find orphans — orders whose customer doesn't exist:

orders.dropDuplicates().join(customers, "customer_id", "left_anti").select(
    "order_id", "customer_id"
).show()

Why not just left-join and filter nulls?

You can — that's the previous lesson. But anti join says your intent directly ("rows with no match"), doesn't drag in the right-side columns, and lets Spark pick an efficient plan. For "does it exist / not exist" questions, reach for semi / anti.

Your turn

Find the customers who have never placed an order. Anti-join customers against orders on customer_id and return customer_id and name. Assign to result.

result = customers  # <- left_anti join orders on customer_id, select customer_id & name
result.show()

result = customers.join(orders, "customer_id", "left_anti").select(
    "customer_id", "name"
)