SQL NULL in WHERE: Why = NULL Matches Nothing

WHERE looks like the simplest clause in SQL: a yes-or-no test per row, keep the yeses. Then a column contains NULL, and queries start losing rows without an error in sight. WHERE mites = NULL returns nothing, even when the column is full of NULLs. WHERE mites <> 12 quietly drops them too. The reason is one rule that most bugs trace back to: SQL tests are not true or false, they are true, false or unknown, and WHERE keeps only true.

The problem it solves

NULL means "no value known": a hive that was never inspected, a customer with no phone number, an order not yet shipped. The mistakes it causes look like this:

• Asking for missing values with =. col = NULL never matches, so a report of "hives with no mite count" comes back empty.
• Losing rows through negation. NOT (mites > 40) and mites <> 12 both skip the uninspected hive, although it is certainly not over 40 and certainly not recorded as 12.
• Averages that ignore rows. AVG(mites) divides by the rows that have a value, not by all rows.
• NOT IN against a set containing NULL, which returns no rows at all, as the subqueries article shows.

The intuition

Read NULL as unknown, and every rule follows from asking "could this be decided?":

• Any comparison with unknown is unknown. Is an unknown count greater than 40? Unknown. Equal to another unknown? Also unknown, which is why NULL = NULL is not true.
• AND is false if either side is false, true if both are true, and unknown otherwise. unknown AND false is false, because the answer does not depend on the unknown.
• OR is true if either side is true, false if both are false, unknown otherwise.
• NOT unknown is unknown. Negating a question you cannot answer does not answer it.
• WHERE keeps a row only when the result is true. Unknown is treated like false for filtering, but it is not false: NOT does not flip it into true.

The fix is to ask the question you mean. IS NULL and IS NOT NULL are always true or false. For "different from 12, counting missing as different", say so: mites <> 12 OR mites IS NULL, or use a null-safe comparison.

Watch it run

The animation filters the lesson's apiary with mites > 40 AND queen_seen = 0. WHERE runs a yes-or-no test on every candidate row and keeps only the passes. Willow has 12 mites and the threshold is 40, so it fails the first test. Chalk clears 40, but its queen was sighted, so the AND fails anyway. Beacon has three mites and its card stays in the drawer. Long Mead has 51 mites and no queen sighted: both halves are true. One row survives; sixty hives, one card pulled, decided before anyone walks into the field. Then the odd one out: Hollow was never inspected, so its mite count is NULL. mites = NULL is never true, because NULL means unknown and unknown equals nothing at all; it matches zero rows. WHERE mites IS NULL is the question you actually meant to ask. The last frame is the lesson's other rule: filter here, not in Python, because rows you drop later were still read, serialised and shipped.

The code

Every query below runs in Python's sqlite3 module (SQLite 3.37). The lesson's table plus Hollow, and the traps one by one:

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
  CREATE TABLE hive (name TEXT, mites INTEGER, queen_seen INTEGER);
  INSERT INTO hive VALUES ('Willow', 12, 1), ('Chalk', 47, 1), ('Beacon', 3, 0),
                          ('Long Mead', 51, 0), ('Hollow', NULL, 0);
""")

def names(where):
    return [r[0] for r in db.execute("SELECT name FROM hive WHERE %s ORDER BY rowid" % where)]

print(names("mites > 40 AND queen_seen = 0"))   # ['Long Mead']
print(names("mites = NULL"))                    # []
print(names("mites IS NULL"))                   # ['Hollow']
print(names("mites <> 12"))                     # ['Chalk', 'Beacon', 'Long Mead']
print(names("NOT (mites > 40)"))                # ['Willow', 'Beacon']
print(names("mites > 40 OR queen_seen = 0"))    # ['Chalk', 'Beacon', 'Long Mead', 'Hollow']
print(names("mites IS NOT 12"))                 # ['Chalk', 'Beacon', 'Long Mead', 'Hollow']

Hollow has queen_seen = 0, yet the first query drops it: unknown AND true is unknown. The OR query keeps it: unknown OR true is true. IS NOT is SQLite's null-safe "different from". The truth table and the aggregates, straight from the engine, where None is NULL, 0 is false and 1 is true:

print(db.execute("SELECT NULL = NULL, NULL AND 0, NULL AND 1, NULL OR 1, NULL OR 0, NOT NULL").fetchone())
# (None, 0, None, 1, None, None)
print(db.execute("SELECT COUNT(*), COUNT(mites), AVG(mites), AVG(COALESCE(mites, 0)) FROM hive").fetchone())
# (5, 4, 28.25, 22.6)

COUNT(*) counts rows, COUNT(mites) counts values, and AVG divides 113 by 4, not 5. COALESCE turns the unknown into a zero, which is a decision about your data, not a fix.

Checked on 300 seeded random tables with NULLs in two columns and 3,000 random WHERE clauses, nested up to three levels of AND, OR and NOT over comparisons and IS NULL tests. A Python evaluator of three-valued logic, with None as unknown, must pick exactly the rows SQLite returns:

import random

def and3(a, b):
    if a is False or b is False:
        return False
    return None if a is None or b is None else True

def or3(a, b):
    if a is True or b is True:
        return True
    return None if a is None or b is None else False

def not3(a):
    return None if a is None else not a

OPS = {"=": lambda x, y: x == y, "<>": lambda x, y: x != y,
       "<": lambda x, y: x < y, ">": lambda x, y: x > y}

def predicate(depth):
    """A random WHERE clause, as SQL text and as a Python three-valued function."""
    kind = random.choice(["cmp", "null"] if depth == 0 else ["cmp", "null", "and", "or", "not"])
    if kind == "cmp":
        col, op, v = random.choice("ab"), random.choice(list(OPS)), random.randint(0, 3)
        f = lambda r: None if r[col] is None else OPS[op](r[col], v)   # NULL compares unknown
        return "%s %s %d" % (col, op, v), f
    if kind == "null":
        col, neg = random.choice("ab"), random.random() < 0.5
        return "%s IS %sNULL" % (col, "NOT " if neg else ""), lambda r: (r[col] is None) != neg
    if kind == "not":
        s, f = predicate(depth - 1)
        return "NOT (%s)" % s, lambda r: not3(f(r))
    (s1, f1), (s2, f2) = predicate(depth - 1), predicate(depth - 1)
    comb = and3 if kind == "and" else or3
    return "(%s) %s (%s)" % (s1, kind.upper(), s2), lambda r: comb(f1(r), f2(r))

random.seed(29)
ok = True
for _ in range(300):
    t = sqlite3.connect(":memory:")
    t.execute("CREATE TABLE t (id INTEGER, a INTEGER, b INTEGER)")
    rows = [{"id": i, "a": random.choice([None, 0, 1, 2, 3]), "b": random.choice([None, 0, 1, 2, 3])}
            for i in range(random.randint(0, 25))]
    t.executemany("INSERT INTO t VALUES (:id, :a, :b)", rows)
    for _ in range(10):
        sql, f = predicate(random.randint(0, 3))
        got = [r[0] for r in t.execute("SELECT id FROM t WHERE %s ORDER BY id" % sql)]
        ok &= got == [r["id"] for r in rows if f(r) is True]     # WHERE keeps only TRUE
print(ok)                                                        # True

The complexity

• Evaluation: one pass over the candidate rows, O(n) without an index; NULL handling adds nothing measurable.
• Indexes: an equality or range condition on an indexed column becomes a search instead of a scan, as in SQL indexes. Whether IS NULL can use an index depends on the engine and on whether the index stores NULLs.
• Network: filtering in the database ships only matching rows. Filtering in the application ships all of them first.

Where it goes wrong

• = NULL and <> NULL. Both are always unknown. Use IS NULL and IS NOT NULL.
• Negations that should include missing values. Add OR col IS NULL explicitly when "not 12" should include "unknown".
• Joins on nullable keys. NULL never equals NULL, so rows with a missing key never join, as the joins article shows.
• COALESCE everywhere. Replacing unknown with zero changes averages and sums; do it only when zero is truly the meaning.

As of September 2026, the standard null-safe comparison is IS [NOT] DISTINCT FROM, supported by PostgreSQL and recent SQLite versions; SQLite also accepts IS and IS NOT between any values, and MySQL spells null-safe equality <=>.

When it shows up in interviews

As a trick question ("why does this query return nothing?"), as the reason a NOT IN returns an empty set, and inside every "find customers without orders" problem, where the answer is a left join with WHERE o.id IS NULL. It also sits under GROUP BY, because aggregates skip NULLs. The SQL cheat sheet collects the NULL rules next to the interview queries that trip over them.

How to say it in an interview

"SQL uses three-valued logic: a comparison with NULL is unknown, not false, and WHERE keeps only rows where the condition is true. So col = NULL never matches; I'd write col IS NULL. Negation doesn't help, because NOT unknown is still unknown, so if missing values should count as 'not 12', I add OR col IS NULL or use a null-safe comparison. I also watch aggregates: COUNT(col) and AVG(col) skip NULLs, while COUNT(*) counts rows. And I filter in the database, so only matching rows cross the network."