Definition
- Anti-join – Returns left rows that have no matching row in the right table.
- LEFT JOIN form –
LEFT JOIN ... WHERE right.key IS NULLkeeps every left row, then filters to only those whose right-side columns are NULL (no match). - NOT EXISTS form –
WHERE NOT EXISTS (SELECT 1 FROM right WHERE ...)directly expresses "no match exists" and is NULL-safe. - The WHERE trap – Any condition on the right table's columns placed in
WHEREremoves the NULL-filled unmatched rows, converting the LEFT JOIN into an INNER JOIN. - The ON clause fix – Right-table conditions that restrict which rows count as a match belong in
ON, notWHERE. - Impossible combination –
WHERE o.status = 'X' AND o.id IS NULLcontradicts itself becauseo.status = 'X'requires a match whileo.id IS NULLrequires no match.
National Bank of India wants customers who have never placed a loan application. You list all customers and attach their loan applications with a LEFT JOIN; customers with no applications show blank fields. You keep only the blank ones – those are the "never applied" customers. The trap: if you add "where the loan status is REJECTED" in the WHERE clause, you demand that a loan exists, which contradicts "no loan." That status condition must live in the ON clause instead.
Understanding the Concepts
What is an anti-join?
An anti-join returns rows from the left table that have no matching row in the right table. It answers questions like "which customers have never transacted?" or "which accounts have no loans?"
-- Anti-join: customers who have never placed a loan application
SELECT c.customer_id, c.full_name
FROM customers c
LEFT JOIN loan_applications la ON la.customer_id = c.customer_id
WHERE la.loan_id IS NULL;
Expected output:
customer_id | full_name
-------------+--------------
1003 | Pankaj Gupta
-- Pankaj has never applied for a loan
How does the LEFT JOIN anti-join work?
The LEFT JOIN keeps every left row. For customers with loan applications, the loan columns are filled with real data. For customers without applications, the loan columns are all NULL. The WHERE la.loan_id IS NULL filter then keeps exactly the unmatched rows.
-- Step 1: see the raw LEFT JOIN output
SELECT c.customer_id, c.full_name, la.loan_id, la.status
FROM customers c
LEFT JOIN loan_applications la ON la.customer_id = c.customer_id;
customer_id | full_name | loan_id | status
-------------+--------------+---------+-----------
1001 | Rajesh Kumar | L-001 | APPROVED
1001 | Rajesh Kumar | L-002 | PENDING
1002 | Priya Sharma | L-003 | REJECTED
1003 | Pankaj Gupta | NULL | NULL ← no match
-- Step 2: filter to keep only unmatched rows
WHERE la.loan_id IS NULL;
-- Returns only Pankaj (no loan applications)
How does NOT EXISTS work for anti-joins?
NOT EXISTS expresses the same intent more directly and is NULL-safe.
-- NOT EXISTS: customers with no loan application
SELECT c.customer_id, c.full_name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM loan_applications la
WHERE la.customer_id = c.customer_id
);
What is the LEFT JOIN WHERE-condition trap?
The ON clause and the WHERE clause behave very differently for a LEFT JOIN. A condition in ON affects which right rows are considered a match – if it fails, the left row still appears with NULLs. A condition in WHERE is applied after the join, and because unmatched left rows have NULL right columns, any WHERE condition on a right column (other than IS NULL) removes them.
-- WRONG: WHERE filter on right table silently turns LEFT JOIN into INNER JOIN
SELECT c.customer_id, c.full_name, la.loan_id
FROM customers c
LEFT JOIN loan_applications la ON la.customer_id = c.customer_id
WHERE la.status = 'REJECTED';
-- Pankaj (no loans) has NULL status. NULL = 'REJECTED' is UNKNOWN.
-- Pankaj is DROPPED. LEFT JOIN behaves as INNER JOIN.
WRONG output (Pankaj missing!):
customer_id | full_name | loan_id
-------------+--------------+--------
1002 | Priya Sharma | L-003
-- CORRECT: move condition into ON clause
SELECT c.customer_id, c.full_name, la.loan_id
FROM customers c
LEFT JOIN loan_applications la
ON la.customer_id = c.customer_id AND la.status = 'REJECTED'
ORDER BY c.customer_id;
CORRECT output (Pankaj preserved with NULL):
customer_id | full_name | loan_id
-------------+--------------+--------
1001 | Rajesh Kumar | NULL ← Rajesh has loans but none REJECTED
1002 | Priya Sharma | L-003 ← Priya has a REJECTED loan
1003 | Pankaj Gupta | NULL ← Pankaj has no loans at all
Why is WHERE o.status = 'X' AND o.id IS NULL impossible?
This combination contradicts itself: o.status = 'X' demands a non-NULL status from a matched row, while o.id IS NULL demands an unmatched row with NULL columns. Both cannot be true at the same time, so the result is always empty.
-- IMPOSSIBLE: contradictory conditions
SELECT c.customer_id
FROM customers c
LEFT JOIN loan_applications la ON la.customer_id = c.customer_id
WHERE la.status = 'REJECTED' AND la.loan_id IS NULL;
-- la.status = 'REJECTED' → requires a matched row
-- la.loan_id IS NULL → requires an unmatched row
-- Result: ALWAYS empty
-- CORRECT: "customers with no REJECTED loan application"
SELECT c.customer_id, c.full_name
FROM customers c
LEFT JOIN loan_applications la
ON la.customer_id = c.customer_id AND la.status = 'REJECTED'
WHERE la.loan_id IS NULL;
-- The ON clause limits matches to REJECTED loans only.
-- IS NULL keeps customers who have no such loan.
How do you express this in Java?
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 ReactivationService {
// Find customers who have never transacted (for reactivation campaign)
public List<String> findNeverTransacted(Connection conn) throws SQLException {
String sql = """
SELECT c.customer_id, c.full_name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM transactions t
WHERE t.customer_id = c.customer_id
)
ORDER BY c.customer_id
""";
List<String> targets = new ArrayList<>();
try (PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
targets.add(rs.getString("customer_id") + ": "
+ rs.getString("full_name"));
}
}
return targets;
}
}
What is the rule for ON versus WHERE in LEFT JOINs?
Filters that restrict which right rows count as a match go in ON. Filters that keep or drop the already-joined result go in WHERE. For an anti-join, the only WHERE condition on the right side should be IS NULL.
-- ON: restricts which right rows match
LEFT JOIN loan_applications la
ON la.customer_id = c.customer_id
AND la.status = 'REJECTED' -- only REJECTED loans count as a match
-- WHERE: filters the final result
WHERE la.loan_id IS NULL -- keep rows where no match was found
What should a senior engineer know?
A senior engineer knows that any right-table filter in WHERE converts a LEFT JOIN to an INNER JOIN unless the filter is IS NULL, that NOT EXISTS is often clearer and NULL-safe, and that the ON-versus-WHERE distinction is the single most common source of silent LEFT JOIN bugs in reporting queries.
Visual Overview
┌─────────────────────────────────────────────────────┐ │ ON vs WHERE in LEFT JOIN │ ├─────────────────────────────────────────────────────┤ │ │ │ LEFT JOIN loan_applications la │ │ ON la.customer_id = c.customer_id │ │ AND la.status = 'REJECTED' ← ON: which rows │ │ count as a match │ │ │ │ customers loan_apps (only REJECTED) │ │ ┌──────────┐ ┌──────────┐ │ │ │ Rajesh │──×──→ │ (none) │ → NULL (no REJECTED)│ │ │ Priya │──✓──→ │ L-003 │ → matched │ │ │ Pankaj │──×──→ │ (none) │ → NULL (no loans) │ │ └──────────┘ └──────────┘ │ │ │ │ WHERE la.loan_id IS NULL ← WHERE: filter result │ │ → Rajesh (no REJECTED loan) │ │ → Pankaj (no loans at all) │ │ │ │ If WHERE la.status = 'REJECTED' instead: │ │ → Pankaj's NULL status fails → DROPPED! │ │ → LEFT JOIN becomes INNER JOIN silently │ │ │ └─────────────────────────────────────────────────────┘
Key Takeaway
An anti-join returns the left rows that have no matching row in the right table, and there are two standard ways to express it. The LEFT JOIN form keeps every left row, fills the right columns with NULL where there is no match, then filters with WHERE right.key IS NULL. The NOT EXISTS form says the same thing more directly and is NULL-safe. At National Bank of India, Rajesh uses anti-joins to find customers who have never transacted for reactivation campaigns.
The classic trap is about where you put conditions on the right table. A condition in ON decides which right rows count as a match – if it fails, the left row still survives with NULLs. A condition in WHERE is applied after the join, and because unmatched rows carry NULL right columns, any WHERE condition other than IS NULL removes them, silently converting the LEFT JOIN into an INNER JOIN. So WHERE la.status = 'REJECTED' AND la.loan_id IS NULL can never match anything, because one condition demands a matched row and the other demands an unmatched one.
The correct approach is to move the status condition into ON: LEFT JOIN loan_applications la ON la.customer_id = c.customer_id AND la.status = 'REJECTED' WHERE la.loan_id IS NULL. Now the join only matches REJECTED loans, and customers without such a loan are correctly preserved.
The senior rule is that right-table filters restricting which rows count as a match go in ON, filters that keep or drop the result go in WHERE, and for an anti-join the only right-side WHERE condition should be IS NULL.
Remember one sentence: Anti-join is LEFT JOIN ... WHERE right.key IS NULL or NOT EXISTS, and any right-table filter must go in ON or it silently turns the outer join into an inner join.
---