Replace SQLite NOT IN when the subquery can return NULL

By Batu · English technical notes

Also published in our primary archive.

Replace SQLite NOT IN when the subquery can return NULL

When a SQLite NOT IN subquery can return NULL, use a correlated NOT EXISTS query when you mean “there is no matching child.” A NULL makes the relevant NOT IN comparisons unknown, so the WHERE clause keeps no rows in this fixture.

The fixture has parents 1, 2, and 3. Its child keys are 1 and NULL. Parent 1 has a match and should be excluded. Parents 2 and 3 have no matching child and should remain. The naïve NOT IN query instead prints an empty list. The corrected query compares c.parent_id = p.id inside NOT EXISTS; a null child key is not equal to either parent ID, so the corrected result is [(2,), (3,)].

This is three-valued SQL logic, not a Python None handling rule. SQLite’s expression documentation describes the IN/NOT IN result matrix: when no match exists but the right-hand side contains NULL, the result can be NULL. A WHERE clause retains only true expressions, not false or null ones.

This example uses only stable Python sqlite3 APIs; no newer Python version requirement applies. If NULL child keys are invalid by design, a schema constraint may be appropriate, but that is a separate data-model decision. The assertion here proves this fixed fixture’s query results only.

Source: SQLite expressions: IN and NOT IN.

AI assistance disclosure: This article was drafted with AI assistance and verified against the cited documentation and a synthetic in-memory example.

import sqlite3

con = sqlite3.connect(":memory:")
con.executescript(
    """
    CREATE TABLE parent (id INTEGER PRIMARY KEY);
    CREATE TABLE child (parent_id INTEGER);
    INSERT INTO parent (id) VALUES (1), (2), (3);
    INSERT INTO child (parent_id) VALUES (1), (NULL);
    """
)

not_in = con.execute(
    "SELECT id FROM parent "
    "WHERE id NOT IN (SELECT parent_id FROM child) ORDER BY id"
).fetchall()
not_exists = con.execute(
    "SELECT p.id FROM parent AS p "
    "WHERE NOT EXISTS ("
    "  SELECT 1 FROM child AS c WHERE c.parent_id = p.id"
    ") ORDER BY p.id"
).fetchall()

print("NOT IN:", not_in)
print("NOT EXISTS:", not_exists)

assert not_in == []
assert not_exists == [(2,), (3,)]
con.close()

NOT IN: []
NOT EXISTS: [(2,), (3,)]