What is the Difference Between a Semi-Join and an Anti-Join?
Learn the difference between SQL semi-joins and anti-joins, how EXISTS and NOT EXISTS implement them, and interview tips.
Expected Interview Answer
A semi-join returns rows from the first table that have at least one matching row in the second table, without duplicating rows or pulling in any columns from the second table, while an anti-join returns rows from the first table that have no matching row in the second table at all, and neither is a distinct SQL keyword — both are typically expressed using EXISTS, NOT EXISTS, IN, or NOT IN.
A regular INNER JOIN on a one-to-many relationship can duplicate rows from the first table when multiple matches exist, but a semi-join (usually written as WHERE EXISTS (subquery)) stops at the first match and returns each qualifying row from the first table exactly once. An anti-join (WHERE NOT EXISTS (subquery)) is the logical opposite: it returns rows that have zero matches, which is useful for finding orphaned or unreferenced records, like customers who never placed an order. Query planners often optimize EXISTS/NOT EXISTS into genuine semi-join and anti-join execution plans, which can be significantly faster than an equivalent JOIN plus DISTINCT or a JOIN plus IS NULL check.
- Semi-join filters without duplicating rows or column bloat
- Anti-join directly finds unmatched or orphaned records
- Both often outperform JOIN-based equivalents on large tables
- EXISTS/NOT EXISTS communicate filtering intent more clearly than IN/NOT IN
AI Mentor Explanation
Imagine a selector who wants a list of every player who has scored at least one century, without listing the century itself or duplicating a player who scored several centuries. Checking each player against the innings records and stopping the moment one century is found is a semi-join: it confirms existence without pulling in extra data. The opposite question, listing every player who has never scored a century, is an anti-join: it confirms the complete absence of a match rather than its presence.
Step-by-Step Explanation
Step 1
Identify the filtering intent
Decide whether you need rows WITH at least one match (semi-join) or rows WITH NO match at all (anti-join).
Step 2
Write a correlated subquery
Reference the outer table’s row inside a subquery against the second table, e.g. WHERE customer_id = o.customer_id.
Step 3
Wrap with EXISTS or NOT EXISTS
Use WHERE EXISTS (...) for a semi-join, or WHERE NOT EXISTS (...) for an anti-join.
Step 4
Verify no duplication and no extra columns
Confirm the result only contains rows from the first table, each appearing once, with no columns pulled from the second table.
What Interviewer Expects
- Correct definitions of semi-join (has a match) and anti-join (has no match)
- Awareness that neither is a dedicated SQL keyword; both use EXISTS/NOT EXISTS or IN/NOT IN
- Understanding that semi-join avoids row duplication unlike a plain INNER JOIN
- A concrete example, such as customers with or without orders
Common Mistakes
- Using an INNER JOIN plus DISTINCT instead of EXISTS, causing worse performance on large tables
- Using NOT IN with a subquery that can return NULLs, silently producing zero rows
- Confusing a semi-join with a regular INNER JOIN that happens to only pull one column
- Not considering that anti-join with NOT EXISTS handles NULLs correctly, unlike NOT IN
Best Answer (HR Friendly)
“A semi-join gets me rows from one table that have at least one match in another table, without duplicating rows or pulling in extra columns, and I usually write it with WHERE EXISTS. An anti-join is the opposite: it gets me rows that have no match at all, written with WHERE NOT EXISTS, which is perfect for finding things like customers who never placed an order.”
Code Example
-- Semi-join: customers who have placed at least one order
SELECT c.customer_id, c.name
FROM Customers c
WHERE EXISTS (
SELECT 1 FROM Orders o WHERE o.customer_id = c.customer_id
);
-- Anti-join: customers who have never placed an order
SELECT c.customer_id, c.name
FROM Customers c
WHERE NOT EXISTS (
SELECT 1 FROM Orders o WHERE o.customer_id = c.customer_id
);Follow-up Questions
- Why is NOT EXISTS generally safer than NOT IN when the subquery can return NULLs?
- How does a semi-join avoid the row duplication a plain INNER JOIN can cause?
- Can a query optimizer turn an INNER JOIN plus DISTINCT into a semi-join plan?
- How would you write an anti-join using a LEFT JOIN and IS NULL instead of NOT EXISTS?
MCQ Practice
1. What does a semi-join return?
A semi-join returns each qualifying row from the first table once, confirming a match exists without pulling in extra columns.
2. Which SQL construct is commonly used to express an anti-join?
An anti-join is typically written as WHERE NOT EXISTS, returning rows with zero matches in the subquery.
3. Why can NOT IN be riskier than NOT EXISTS for an anti-join?
If the subquery in a NOT IN returns any NULL, the entire NOT IN condition evaluates to unknown for every row, unlike NOT EXISTS.
Flash Cards
What is a semi-join? — Rows from the first table with at least one match in the second, returned without duplication or extra columns.
What is an anti-join? — Rows from the first table with no match at all in the second table.
How are semi/anti-joins usually written? — With WHERE EXISTS (semi-join) or WHERE NOT EXISTS (anti-join).
Why prefer NOT EXISTS over NOT IN? — NOT EXISTS handles NULLs in the subquery correctly; NOT IN can silently return zero rows.