Definition
- Semi-join – A join that returns left rows having at least one match on the right, without multiplying rows or including right-side columns.
EXISTS– Expresses a true semi-join: returns each qualifying left row exactly once and stops scanning the right table at the first match.- INNER JOIN duplication – An INNER JOIN produces one output row per matching pair, so a left row with N matches appears N times.
DISTINCTpenalty – To deduplicate the inflated result, the database must sort or hash the entire intermediate set, which is expensive.- Short-circuit –
EXISTScan stop scanning a parent's children the instant it finds one match, avoiding wasted reads. NOT EXISTS– The anti-join counterpart that returns rows with no match, also benefiting from short-circuit evaluation.
National Bank of India wants a list of accounts that have made any transaction. If you INNER JOIN accounts to transactions, Rajesh, who has 50 transactions, appears 50 times in the result. You then bolt on DISTINCT to collapse him to one row – wasteful. With EXISTS, the clerk just confirms Rajesh has at least one transaction and lists him once, stopping at the very first slip they find.
Understanding the Concepts
What is a semi-join?
A semi-join returns left rows that have at least one match on the right, without multiplying rows or including columns from the right table. It answers the question "which parents have at least one child?" rather than "give me every parent-child pair."
-- Semi-join with EXISTS: accounts that have at least one transaction
SELECT a.account_id, a.holder_name
FROM accounts a
WHERE EXISTS (
SELECT 1 FROM transactions t WHERE t.account_id = a.account_id
);
Expected output:
account_id | holder_name
------------+--------------
SAV2001 | Rajesh Kumar
CUR3001 | Priya Sharma
-- Each account listed once, regardless of transaction count
Why is INNER JOIN wrong for semi-joins?
An INNER JOIN combines rows: for every pair of a left row and a matching right row, it emits one output row. When you only want to know which left rows have a match, the duplication is both wrong and slow.
-- WRONG: INNER JOIN duplicates accounts
SELECT a.account_id, a.holder_name
FROM accounts a
JOIN transactions t ON t.account_id = a.account_id;
WRONG output (Rajesh appears 50 times!):
account_id | holder_name
------------+--------------
SAV2001 | Rajesh Kumar
SAV2001 | Rajesh Kumar
SAV2001 | Rajesh Kumar
... (50 rows for Rajesh)
CUR3001 | Priya Sharma
CUR3001 | Priya Sharma
CUR3001 | Priya Sharma
-- "Fix" with DISTINCT: works but wasteful
SELECT DISTINCT a.account_id, a.holder_name
FROM accounts a
JOIN transactions t ON t.account_id = a.account_id;
-- Must first generate 50+ duplicate rows, then sort/hash to remove them
How does EXISTS short-circuit?
The engine can stop scanning the right table the instant it finds the first match. For Rajesh with 50 transactions, EXISTS inspects one transaction and moves on. The INNER JOIN reads all 50 and then DISTINCT throws 49 away.
-- EXISTS: reads only the first matching transaction per account
SELECT a.account_id, a.holder_name
FROM accounts a
WHERE EXISTS (
SELECT 1 FROM transactions t
WHERE t.account_id = a.account_id
-- Engine stops here after finding the first match
);
How is EXISTS different from INNER JOIN + DISTINCT?
| Aspect | EXISTS | INNER JOIN + DISTINCT |
|---|---|---|
| Output rows | One per qualifying parent | N per parent (then deduplicated) |
| Intermediate size | Small (one per parent) | Large (all matching pairs) |
| Short-circuit | Yes (stops at first match) | No (reads all matches) |
| DISTINCT needed | No | Yes (expensive sort/hash) |
| Right columns available | No (not in SELECT) | Yes (but not wanted) |
| Plan chosen by optimizer | Semi-join (hash/nested-loop) | Full join + deduplication |
package com.bank.service;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
public class ActiveAccountService {
// CORRECT: EXISTS for semi-join
public List<String> getActiveAccounts(Connection conn) throws SQLException {
String sql = """
SELECT a.account_id, a.holder_name
FROM accounts a
WHERE EXISTS (
SELECT 1 FROM transactions t
WHERE t.account_id = a.account_id
)
ORDER BY a.account_id
""";
List<String> active = new ArrayList<>();
try (PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
active.add(rs.getString("account_id") + ": "
+ rs.getString("holder_name"));
}
}
return active;
}
}
When should you use a JOIN instead of EXISTS?
Use a JOIN when you actually need columns from the child table in the result. If you need the transaction details alongside the account, a JOIN is correct.
-- JOIN is correct when you need child columns
SELECT a.account_id, a.holder_name, t.txn_id, t.amount, t.txn_date
FROM accounts a
JOIN transactions t ON t.account_id = a.account_id
ORDER BY t.txn_date DESC;
-- Here the duplication is desired: one row per transaction
What about NOT EXISTS for anti-joins?
NOT EXISTS returns rows with no match and benefits from the same short-circuit: the engine stops scanning the moment it finds a match and moves on to the next parent.
-- NOT EXISTS: accounts with NO transactions (dormant)
SELECT a.account_id, a.holder_name
FROM accounts a
WHERE NOT EXISTS (
SELECT 1 FROM transactions t WHERE t.account_id = a.account_id
);
What about IN versus EXISTS?
IN can express a semi-join too: WHERE account_id IN (SELECT account_id FROM transactions). Modern optimizers often rewrite IN to the same semi-join plan as EXISTS. The difference is NULL handling: NOT IN with NULLs in the subquery can produce unexpected empty results, while NOT EXISTS is NULL-safe.
-- IN: also a semi-join, but beware of NULLs with NOT IN
SELECT a.account_id FROM accounts a
WHERE a.account_id IN (SELECT t.account_id FROM transactions t);
-- NOT IN trap: if subquery returns a NULL, no rows match!
SELECT a.account_id FROM accounts a
WHERE a.account_id NOT IN (SELECT t.account_id FROM transactions t);
-- If any t.account_id is NULL, this returns ZERO rows
-- NOT EXISTS is NULL-safe
SELECT a.account_id FROM accounts a
WHERE NOT EXISTS (SELECT 1 FROM transactions t WHERE t.account_id = a.account_id);
What should a senior engineer know?
A senior engineer knows to use EXISTS for semi-joins and NOT EXISTS for anti-joins, that the optimizer recognizes these as semi-join patterns and picks efficient plans, that NOT IN with NULLs is a trap, and that an index on the child's join column (e.g., transactions(account_id)) is critical for short-circuit performance.
Visual Overview
┌─────────────────────────────────────────────────────┐ │ INNER JOIN vs EXISTS for Semi-Join │ ├─────────────────────────────────────────────────────┤ │ │ │ Account: Rajesh (SAV2001) has 50 transactions │ │ │ │ INNER JOIN: │ │ ┌──────────┐ ┌──────────┐ │ │ │ SAV2001 │──→ │ TXN-001 │ → output row 1 │ │ │ │──→ │ TXN-002 │ → output row 2 │ │ │ │──→ │ TXN-003 │ → output row 3 │ │ │ │ │ ... │ → ... │ │ │ │──→ │ TXN-050 │ → output row 50 │ │ └──────────┘ └──────────┘ │ │ Then DISTINCT: sort 50 rows → keep 1. Wasteful. │ │ │ │ EXISTS: │ │ ┌──────────┐ ┌──────────┐ │ │ │ SAV2001 │──→ │ TXN-001 │ → FOUND! Stop. │ │ └──────────┘ │ TXN-002 │ ← never read │ │ │ │ ... │ ← never read │ │ ▼ └──────────┘ │ │ Output: 1 row (SAV2001). No DISTINCT needed. │ │ │ └─────────────────────────────────────────────────────┘
Key Takeaway
A semi-join returns the left rows that have at least one match on the right, without multiplying rows. The key insight is that an INNER JOIN is not a semi-join. An INNER JOIN produces one output row per matching pair, so Rajesh with 50 transactions at National Bank of India appears 50 times, forcing a DISTINCT to collapse duplicates.
That DISTINCT is exactly the performance problem. To remove duplicates, the database must materialize and sort or hash the entire inflated intermediate result, and the size grows with the number of child rows. EXISTS avoids all of this because it returns each qualifying left row exactly once, no matter how many children match, so there is nothing to deduplicate. On top of that, EXISTS short-circuits – the engine stops scanning children the instant it finds the first match.
The rule is straightforward: if you need columns from the child table, use a JOIN. If you only need to test that a related row exists, use EXISTS, which lets the optimizer choose a semi-join plan and avoids the duplicate-rows-plus-DISTINCT penalty. The same reasoning makes NOT EXISTS the right choice for anti-joins, and it is also NULL-safe unlike NOT IN.
In Rajesh's statement batch at National Bank of India, switching from INNER JOIN + DISTINCT to EXISTS both fixed a duplicate-statement bug and sped up the job, because the semi-join inspected far fewer child rows.
Remember one sentence: For "does a related row exist?", use EXISTS – it returns each row once and short-circuits, while an INNER JOIN duplicates rows and needs a DISTINCT to clean up.
---