Anti-Join & Semi-Join
Python · Combining DataFrames (SQL Joins & Unions)
Theory
pandas has no how='anti' or how='semi', but both are short patterns. A semi-join keeps rows of the left table that have a match on the right, without adding any right-hand columns and without duplicating rows: customers[customers['cust_id'].isin(orders['cust_id'])] lists customers who have ordered at least once, each once, even if they ordered many times. This is SQL's WHERE EXISTS or IN (subquery).
An anti-join keeps left rows with no match: customers[~customers['cust_id'].isin(orders['cust_id'])] gives customers who never ordered - SQL's NOT EXISTS or LEFT JOIN ... WHERE o.cust_id IS NULL. For multi-column keys, use a left merge with indicator=True and keep rows where _merge == 'left_only', after taking only the key columns from the right and dropping their duplicates so nothing is multiplied.
Why we use it
'Which customers never ordered?', 'which products have no sales?', 'which records are missing from the target system?' are everyday questions. Anti- and semi-joins answer them without inflating rows or adding unwanted columns.
When to use it
Use isin for single-column keys; it is short and fast. Use merge with indicator=True for multi-column keys. If you actually need columns from the right table, use a normal merge instead of a semi-join.
How it works
- Need rows of A based on B
- Do you need columns from B? Yes: Use a normal merge. No: Filter A only.
- Keep rows that HAVE a match? Yes: Semi: A[A.key.isin(B.key)]. No: Anti: A[~A.key.isin(B.key)].
- For multi-column keys use merge(indicator=True)
- Filtered rows of A, each once
Syntax
semi: left[left['key'].isin(right['key'])]
anti: left[~left['key'].isin(right['key'])]
anti (multi-key):
m = left.merge(right[keys].drop_duplicates(), on=keys, how='left', indicator=True)
m[m['_merge'] == 'left_only'].drop(columns='_merge')Example
never_ordered = customers[~customers['cust_id'].isin(orders['cust_id'])]Key points
- Semi-join = filter with isin: no new columns, no duplicated rows
- Anti-join = ~isin, or merge(indicator=True) and keep 'left_only'
- Deduplicate the right-side keys before an indicator merge
- SQL equivalent: WHERE EXISTS (...) / WHERE NOT EXISTS (...)
- Also SQL: LEFT JOIN orders o ... WHERE o.cust_id IS NULL