Definition
- EXISTS – a boolean operator that takes a correlated subquery and returns true when at least one row is produced; short-circuits on the first match.
- NOT EXISTS – returns true when the subquery produces no rows; safe with NULLs.
- IN – tests whether a value appears in a materialized set returned by a subquery.
- NOT IN – dangerous: if the subquery returns any NULL, the entire expression evaluates to UNKNOWN and the query returns no rows.
- Semi-join – EXISTS acts as a semi-join: returns each outer row at most once regardless of how many inner rows match.
- Short-circuit – EXISTS stops scanning the moment the first matching row is found.
Imagine a clerk at National Bank of India checking "does this account have any transaction at all?" With EXISTS, the clerk flips through the transaction file and stops the moment they find the first slip for that account. With IN, the clerk first writes out the full list of account IDs that have transactions and then checks whether the account is on that list. NOT EXISTS is the clerk confirming there is not a single slip for that account.
Understanding the Concepts
What is the EXISTS operator?
EXISTS is used with a correlated subquery and checks only for the presence of rows, not their contents. Because the values are never used, the SELECT list is irrelevant. You conventionally write SELECT 1.
-- Find all 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
);
Result:
account_id | holder_name
SAV2001 | Rajesh Kumar
SAV2002 | Priya Sharma
How does EXISTS short-circuit?
The engine stops scanning the moment it finds the first matching transaction for each account. If an account has 10,000 transactions, EXISTS finds the first one and moves on.
-- EXISTS stops at the first match
-- If SAV2001 has 10,000 transactions:
-- EXISTS scans 1 row and stops
-- COUNT(*) would scan all 10,000
SELECT a.account_id
FROM accounts a
WHERE EXISTS (
SELECT 1 FROM transactions t
WHERE t.account_id = a.account_id
-- Stops after finding the first row for each account
);
How does IN work?
IN evaluates its subquery into a materialized set of values and then tests the outer value for membership in that set.
-- Find accounts that are in the blocked list
SELECT a.account_id, a.holder_name
FROM accounts a
WHERE a.account_id IN (
SELECT b.account_id
FROM blocked_accounts b
);
-- The subquery is evaluated once to produce a set:
-- {'SAV2003', 'SAV2005'}
-- Then each outer row's account_id is tested for membership
How is EXISTS different from IN?
| Aspect | EXISTS | IN |
|---|---|---|
| Evaluation | Correlated, row by row | Materialized set |
| Short-circuit | Yes (stops at first match) | No (builds full set) |
| NULL safety | Safe (checks row existence) | Dangerous with NOT IN |
| Duplicates | Semi-join: no duplicate outer rows | Same (no duplicates) |
| Performance | Wins when inner table is large | Wins when inner set is small |
-- EXISTS: correlated, short-circuits
SELECT a.account_id FROM accounts a
WHERE EXISTS (SELECT 1 FROM transactions t WHERE t.account_id = a.account_id);
-- IN: materialized set
SELECT a.account_id FROM accounts a
WHERE a.account_id IN (SELECT account_id FROM transactions);
-- Modern optimizers often rewrite both into the same semi-join plan
-- Always check the EXPLAIN plan rather than rely on rules of thumb
Why is NOT IN dangerous with NULLs?
If the NOT IN subquery returns even one NULL, every comparison becomes UNKNOWN and the query silently returns zero rows. This is a real correctness bug.
-- Setup: one blocked account has a NULL account_id
INSERT INTO blocked_accounts VALUES (NULL);
INSERT INTO blocked_accounts VALUES ('SAV2003');
-- NOT IN: returns ZERO rows because of the NULL
SELECT a.account_id
FROM accounts a
WHERE a.account_id NOT IN (
SELECT account_id FROM blocked_accounts
);
-- Returns: empty result set!
-- Reason: 'SAV2001' NOT IN (NULL, 'SAV2003')
-- = 'SAV2001' <> NULL AND 'SAV2001' <> 'SAV2003'
-- = UNKNOWN AND TRUE
-- = UNKNOWN → filtered out
-- NOT EXISTS: correct, returns all non-blocked accounts
SELECT a.account_id
FROM accounts a
WHERE NOT EXISTS (
SELECT 1 FROM blocked_accounts b
WHERE b.account_id = a.account_id
);
-- Returns: SAV2001, SAV2002, SAV2004 (correctly)
What is the NOT EXISTS pattern for anti-joins?
NOT EXISTS is the safe way to find rows that have no matching row in another table. It is unaffected by NULLs.
-- Find accounts with NO transactions (dormant accounts)
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
);
-- Returns accounts with zero transactions
-- Safe even if transactions has NULL account_id values
package com.bank.repository;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;
public class DormantAccountRepository {
private static final Logger LOG = Logger.getLogger(
DormantAccountRepository.class.getName());
private final Connection connection;
public DormantAccountRepository(Connection connection) {
this.connection = connection;
}
public List<String> findDormantAccounts() throws SQLException {
// Using NOT EXISTS for NULL-safe anti-join
String sql = "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"
+ ")";
List<String> results = new ArrayList<>();
try (PreparedStatement ps = connection.prepareStatement(sql);
ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
results.add(rs.getString("account_id")
+ " : " + rs.getString("holder_name"));
}
}
LOG.info("Found " + results.size() + " dormant accounts");
return results;
}
}
Output:
Found 2 dormant accounts
SAV2004 : Amit Verma
CUR3002 : Neha Gupta
How does EXISTS act as a semi-join?
EXISTS is a semi-join: it returns each outer row at most once no matter how many inner rows match. This means it never duplicates rows like an INNER JOIN can, and it never needs a DISTINCT.
-- INNER JOIN: duplicates outer rows if multiple matches
SELECT a.account_id, a.holder_name
FROM accounts a
JOIN transactions t ON t.account_id = a.account_id;
-- SAV2001 appears 500 times (once per transaction)
-- EXISTS: each outer row appears at most once
SELECT a.account_id, a.holder_name
FROM accounts a
WHERE EXISTS (
SELECT 1 FROM transactions t
WHERE t.account_id = a.account_id
);
-- SAV2001 appears exactly once
-- No DISTINCT needed
What are common interview follow-up questions?
Interviewers often ask about the performance difference between EXISTS and IN, the NULL trap with NOT IN, and when to use LEFT JOIN WHERE IS NULL as an alternative to NOT EXISTS.
-- Alternative anti-join: LEFT JOIN WHERE IS NULL
SELECT a.account_id
FROM accounts a
LEFT JOIN transactions t ON t.account_id = a.account_id
WHERE t.account_id IS NULL;
-- Equivalent to NOT EXISTS, but NOT EXISTS is clearer
-- Both produce the same execution plan on modern optimizers
Visual Overview
┌──────────────────────────────────────────────────────┐
│ EXISTS vs IN vs NOT EXISTS │
├──────────────────────────────────────────────────────┤
│ │
│ EXISTS (semi-join): │
│ For each outer row: │
│ Scan inner table ──► Found match? ──► STOP │
│ Return outer row (at most once) │
│ │
│ IN: │
│ Step 1: Build set from subquery {A, B, C} │
│ Step 2: For each outer row: is value in set? │
│ │
│ NOT EXISTS (anti-join): │
│ For each outer row: │
│ Scan inner table ──► No match found? ──► RETURN │
│ Safe with NULLs ✓ │
│ │
│ NOT IN (DANGER): │
│ Set from subquery: {A, NULL, C} │
│ value NOT IN {A, NULL, C} │
│ = value<>A AND value<>NULL AND value<>C │
│ = TRUE AND UNKNOWN AND TRUE │
│ = UNKNOWN → row filtered out! │
│ Result: ZERO rows returned ✗ │
└──────────────────────────────────────────────────────┘
Key Takeaway
EXISTS is a boolean operator that takes a correlated subquery and returns true the moment that subquery produces at least one row, stopping immediately on the first match. Because it only cares about presence, the SELECT list inside EXISTS is irrelevant, so we conventionally write SELECT 1. At the National Bank of India, Rajesh uses EXISTS for questions like finding every account that has at least one transaction.
The difference between EXISTS and IN is in how they evaluate. IN materializes a set of values and tests membership, while EXISTS correlates to the outer row and short-circuits. Modern optimizers often rewrite both into the same semi-join plan, so for straightforward non-NULL equality they usually perform identically. Always check the EXPLAIN plan rather than rely on rules of thumb.
The correctness difference is most important with negation. NOT IN is genuinely dangerous: if its subquery returns even one NULL, every comparison becomes UNKNOWN and the entire query silently returns zero rows. At the National Bank of India, a dormancy report once returned zero accounts for exactly this reason, because a bad import had left a NULL account_id in the blocked_accounts table. NOT EXISTS does not suffer this because it simply checks whether any correlated row exists and is unaffected by NULLs elsewhere.
The senior rule is to prefer NOT EXISTS over NOT IN for every anti-join query. EXISTS also acts as a semi-join, returning each outer row at most once no matter how many inner rows match, so it never needs DISTINCT.
Remember one sentence: EXISTS asks "is there at least one?" and stops at the first match, IN asks "is this value in a built set?", and NOT EXISTS asks "is there none?" safely even when NULLs are present.
---