Back to Practical SQL Queries
Q19Question 19 of 34

The NOT IN + NULL Trap and IN vs EXISTS Performance

Key Concepts
  • NOT IN expands to a chain of AND inequalities; one NULL term makes the entire chain UNKNOWN.
  • The query runs without error and silently returns zero rows, making it a dangerous correctness trap.
  • NOT EXISTS tests row presence, not value inequality, so NULLs do not affect the result.
  • The LEFT JOIN anti-join pattern (LEFT JOIN ... WHERE key IS NULL) is also NULL-safe.
  • Modern optimizers often rewrite both IN and EXISTS into identical semi-join plans.
  • The real distinction on current engines is correctness with NULLs, not raw speed.
  • Add NOT NULL constraints on columns used in subquery anti-patterns when the business allows.

Definition

`NOT IN` with any NULL in the set returns zero rows because `x <> NULL` is UNKNOWN: always use `NOT EXISTS`, which is NULL-safe and short-circuits.
  • Three-valued logic – SQL comparisons produce TRUE, FALSE, or UNKNOWN; any comparison with NULL yields UNKNOWN.
  • NOT IN expansion – col NOT IN (a, b, NULL) becomes col <> a AND col <> b AND col <> NULL; the NULL term is UNKNOWN, collapsing the entire AND chain to UNKNOWN.
  • Silent disaster – the query runs without error and returns an empty result, looking like "no such rows" when it is a logic bug.
  • NOT EXISTS is safe – it tests row presence, not value inequality, so NULLs elsewhere do not affect the result.
  • Performance – IN materializes the set; EXISTS correlates and short-circuits; modern optimizers often rewrite both into identical semi-join plans.

Imagine a clerk at National Bank of India checking which accounts are NOT on a blocklist. The blocklist accidentally contains one blank entry. SQL three-valued logic says "is this account different from a blank? I do not know." Because of that one "I do not know," the clerk refuses to confirm anyone is safe, and the whole report comes back empty.

Understanding the Concepts

What is SQL three-valued logic?

SQL uses three-valued logic: a comparison can be TRUE, FALSE, or UNKNOWN. Any comparison with NULL yields UNKNOWN because NULL means "unknown value."

SQL
-- All comparisons with NULL yield UNKNOWN
SELECT
    (5 = NULL)  AS eq,       -- NULL (UNKNOWN)
    (5 <> NULL) AS neq,      -- NULL (UNKNOWN)
    (5 > NULL)  AS gt,       -- NULL (UNKNOWN)
    (NULL = NULL) AS null_eq; -- NULL (UNKNOWN)

Why does NOT IN return no rows when the set contains NULL?

NOT IN expands into a chain of inequality tests joined by AND.

SQL
-- This query at National Bank of India
SELECT account_id, account_name
FROM accounts
WHERE account_id NOT IN (
    SELECT blocked_account_id FROM fraud_blocklist
);

-- Internally expands to:
WHERE account_id <> 1001
  AND account_id <> 1002
  AND account_id <> NULL   -- UNKNOWN!

-- TRUE AND TRUE AND UNKNOWN = UNKNOWN
-- Row does not qualify. Zero rows returned.

Even a row genuinely different from every real value fails to qualify because of the one NULL.

How does IN behave with NULLs?

IN uses OR, which can still be TRUE even with an UNKNOWN branch.

SQL
-- IN expands with OR
WHERE account_id IN (1001, 1002, NULL)
-- Becomes: account_id = 1001 OR account_id = 1002 OR account_id = NULL
-- TRUE OR anything = TRUE, so IN can still return rows

-- NOT IN expands with AND
WHERE account_id NOT IN (1001, 1002, NULL)
-- Becomes: account_id <> 1001 AND account_id <> 1002 AND account_id <> NULL
-- TRUE AND UNKNOWN = UNKNOWN, so NOT IN returns nothing

This asymmetry is why NOT IN is the dangerous one.

How does NOT EXISTS avoid the problem?

NOT EXISTS tests row presence, not value inequality. A NULL in some column does not affect whether the subquery produces a row.

SQL
-- Safe: NOT EXISTS pattern
SELECT a.account_id, a.account_name
FROM accounts a
WHERE NOT EXISTS (
    SELECT 1
    FROM fraud_blocklist f
    WHERE f.blocked_account_id = a.account_id
);

-- Even if fraud_blocklist has NULL values in blocked_account_id,
-- the join condition f.blocked_account_id = a.account_id
-- simply does not match for NULL rows (NULL = 42 is UNKNOWN, not TRUE)
-- So the subquery returns no row, and NOT EXISTS is TRUE: correct!

What is the LEFT JOIN anti-join alternative?

The LEFT JOIN pattern is also NULL-safe.

SQL
-- LEFT JOIN anti-join pattern
SELECT a.account_id, a.account_name
FROM accounts a
LEFT JOIN fraud_blocklist f ON f.blocked_account_id = a.account_id
WHERE f.blocked_account_id IS NULL;

This returns accounts with no matching blocklist entry, regardless of NULLs in the blocklist.

What is the performance difference between IN and EXISTS?

| Aspect | IN | EXISTS |
|---|---|---|
| Execution | Materializes the subquery set, then tests membership | Correlates to each outer row, short-circuits on first match |
| Best for | Small subquery result set | Large inner table (stops early) |
| Modern optimizers | Often rewritten to semi-join | Often rewritten to semi-join |
| Real difference today | Mainly correctness with NULLs, not speed | Mainly correctness with NULLs, not speed |

SQL
-- IN: materializes the set of blocked IDs
SELECT * FROM accounts
WHERE account_id IN (SELECT blocked_account_id FROM fraud_blocklist);

-- EXISTS: correlates and short-circuits
SELECT * FROM accounts a
WHERE EXISTS (
    SELECT 1 FROM fraud_blocklist f
    WHERE f.blocked_account_id = a.account_id
);

-- Check the actual plan to see if the optimizer chose the same semi-join
EXPLAIN ANALYZE
SELECT * FROM accounts a
WHERE EXISTS (
    SELECT 1 FROM fraud_blocklist f
    WHERE f.blocked_account_id = a.account_id
);

How do you prevent this bug in your codebase?

Rajesh adds a lint rule at National Bank of India that flags NOT IN on nullable columns.

JAVA
package com.bank.repository;

import java.sql.*;
import java.util.List;

public class AccountRepository {

    // Wrong: NOT IN with a subquery that might contain NULLs
    public List<Long> findSafeAccountsBad(Connection conn) throws SQLException {
        String sql = "SELECT account_id FROM accounts "
                   + "WHERE account_id NOT IN "
                   + "(SELECT blocked_account_id FROM fraud_blocklist)";
        // Returns ZERO rows if any blocked_account_id is NULL!
        return executeQuery(conn, sql);
    }

    // Correct: NOT EXISTS is NULL-safe
    public List<Long> findSafeAccountsGood(Connection conn) throws SQLException {
        String sql = "SELECT a.account_id FROM accounts a "
                   + "WHERE NOT EXISTS ("
                   + "  SELECT 1 FROM fraud_blocklist f "
                   + "  WHERE f.blocked_account_id = a.account_id"
                   + ")";
        return executeQuery(conn, sql);
    }

    private List<Long> executeQuery(Connection conn, String sql) throws SQLException {
        try (PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            List<Long> ids = new java.util.ArrayList<>();
            while (rs.next()) {
                ids.add(rs.getLong(1));
            }
            return ids;
        }
    }
}

What are the rules a senior engineer follows?

  • Never use NOT IN against a subquery that could contain NULLs.
  • Always use NOT EXISTS or LEFT JOIN ... WHERE key IS NULL for anti-joins.
  • Add a NOT NULL constraint on columns used in subquery anti-patterns if the business allows.
  • Verify with the execution plan before assuming IN is slower than EXISTS or vice versa.
  • On current engines the real distinction is correctness with NULLs, not raw speed.

What are common interview follow-up questions?

  • Can you make NOT IN safe by adding WHERE col IS NOT NULL to the subquery? (Yes: NOT IN (SELECT col FROM t WHERE col IS NOT NULL) works, but NOT EXISTS is still cleaner.)
  • What about NOT IN with a literal list? (NOT IN (1, 2, 3) is safe because there is no NULL in the list.)
  • How does the optimizer treat IN with a large list? (PostgreSQL converts it to = ANY(array) or a hash lookup.)

Visual Overview

DIAGRAM
  NOT IN expansion:
  col NOT IN (1001, 1002, NULL)
       │
       ▼
  col <> 1001  AND  col <> 1002  AND  col <> NULL
       │                │                  │
      TRUE             TRUE             UNKNOWN
       │                │                  │
       └───── AND ──────┘                  │
              │                            │
             TRUE ─────── AND ─────── UNKNOWN
                           │
                        UNKNOWN
                           │
                    Row does NOT qualify
                    ──▶ Zero rows returned!

  NOT EXISTS:
  NOT EXISTS (SELECT 1 FROM blocklist WHERE blocked_id = a.id)
       │
       ▼
  NULL row: blocked_id = a.id ──▶ NULL = 42 ──▶ UNKNOWN ──▶ no match
  Real row: blocked_id = a.id ──▶ 1001 = 42 ──▶ FALSE    ──▶ no match
       │
       ▼
  Subquery returns no rows ──▶ NOT EXISTS = TRUE ──▶ Row qualifies ✓

Key Takeaway

The reason NOT IN returns no rows when the subquery contains a NULL is SQL's three-valued logic. NOT IN expands into a chain of inequality tests joined by AND, and the term col <> NULL evaluates to UNKNOWN. In an AND chain, TRUE AND UNKNOWN collapses to UNKNOWN, so even a row genuinely different from every real value fails to qualify. The query silently returns an empty result.

At the National Bank of India this exact bug made the nightly safe-accounts report come back empty after a bad import left one NULL in the fraud blocklist. Because the query ran without error, it looked like a real result rather than a logic trap.

NOT EXISTS is safer because it never compares values with inequality. It simply asks whether a correlated subquery produces any matching row, which is a clean yes-or-no regardless of NULLs. The equivalent LEFT JOIN with WHERE key IS NULL is also NULL-safe.

On performance, modern optimizers rewrite both IN and EXISTS into semi-join plans and frequently produce identical execution, so on current engines the real distinction is correctness with NULLs and negation, not raw speed. Always verify with the execution plan.

Remember one sentence: NOT IN with any NULL in the set returns zero rows because x <> NULL is UNKNOWN, so always use NOT EXISTS, which is NULL-safe.

---

Share
Use ← → arrow keys to navigate