left_semi / left_anti
Keep the rows that have a match, or the rows that do not, without duplicating anything.
On this page
Show code in
Every code block on the page follows this.
You will learn
- What left_semi and left_anti return
- Why they never duplicate rows, unlike an inner join
- Why NOT IN with nulls returns nothing, and anti join does not
- How Spark executes them
left_semi keeps left rows that have at least one match on the right; left_anti keeps left rows with no match. Only left columns are returned, and each left row appears at most once.What they do
Semi and anti joins answer existence questions: "customers who ordered" and "customers who never ordered". They are filters on the left table, driven by the right table.
Step by step
customers, orders for customers 1, 1, 3
| customer_id | name |
|---|---|
| 1 | Asha |
| 2 | Ben |
| 3 | Chitra |
| 4 | Dev |
left_semi
| customer_id | name |
|---|---|
| 1 | Asha |
| 3 | Chitra |
Asha has two orders but appears once. An inner join would have returned her twice. left_anti returns Ben and Dev.
Run the example
result = customers.join(orders, on="customer_id", how="left_anti")
SELECT * FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id)
Switch to PySpark to edit and run this example in your browser.
Spark SQL also accepts LEFT ANTI JOIN and LEFT SEMI JOIN directly; EXISTS / NOT EXISTS subqueries are planned as the same joins.
Why not an inner join and distinct?
An inner join emits a row per match, so customers with many orders are repeated and you need a distinct afterwards, which is an extra shuffle on wide rows. A semi join stops at the first match and never duplicates.
The NOT IN null trap
In SQL, WHERE customer_id NOT IN (SELECT customer_id FROM orders) returns no rows at all if the subquery contains a null, as our orders table does. x NOT IN (1, 3, null) means x <> 1 AND x <> 3 AND x <> null, and the last part is null for every x.
| Query | Result with a null in orders |
|---|---|
NOT IN (subquery) | Empty: nothing passes |
NOT EXISTS / left_anti | Ben and Dev, as expected |
Spark plans NOT IN as a special null-aware anti join to preserve those semantics. Prefer NOT EXISTS or left_anti, which mean what people intend.
Under the hood
They use the same strategies as other joins: if the right side is small, it is broadcast and the left side is not shuffled. Only the join keys of the right side matter, so select just the key columns from the right before joining to keep the broadcast small.
Common mistakes
Inner join plus distinct for existence
NOT IN with nullable keys
Expecting right-side columns
Key takeaways
- left_semi keeps left rows with a match; left_anti keeps rows without one.
- Only left columns come back, and no row is duplicated.
- NOT IN returns nothing if the subquery has a null; NOT EXISTS does not.
- Small right sides are broadcast, so select only their keys.
Check yourself
3 questions1. A customer has 3 orders. How many times does it appear in a left_semi join with orders?
Show the answer
1. A semi join keeps each left row at most once.
2. The subquery returns 1, 3 and null. What does WHERE id NOT IN (subquery) return?
Show the answer
No rows. id <> null is unknown for every id, so the whole NOT IN is never true.
3. Which columns does left_anti return?
Show the answer
Left only. Semi and anti joins act as filters on the left table.
Practice it
Interview problems that use left_semi / left_anti: write the PySpark, run it, and get graded on hidden tests.
Go deeper
Primary sources: DataFrame.join · JOIN types (SQL)