SQLite: Keep NULL from Hiding Unmatched Rows
A report that excludes blocked IDs can unexpectedly return no rows when the blocked list contains a missing ID. Inspect the NULL policy on both sides before choosing an exclusion query.
Run a nullable exclusion query
The example includes a missing candidate ID as well as a missing blocked ID. Python prints SQL NULL as None. All tables exist only in memory.
Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3.11 or later with SQLite support, a text editor, and standard filesystem tools. No extra packages or SQLite CLI are required.
Create a scratch directory in one terminal. Stop if setup fails before saving the example.
start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1Save as exclude-null-keys.py:
import sqlite3
db = sqlite3.connect(":memory:")
try:
db.executescript("""
CREATE TABLE candidates (id INTEGER);
CREATE TABLE blocked (id INTEGER);
INSERT INTO candidates VALUES (1), (2), (NULL);
INSERT INTO blocked VALUES (1), (NULL);
""")
queries = {
"NOT IN": "SELECT id FROM candidates WHERE id NOT IN (SELECT id FROM blocked)",
"NOT EXISTS": """SELECT c.id FROM candidates AS c
WHERE NOT EXISTS (SELECT 1 FROM blocked AS b WHERE b.id = c.id)
ORDER BY c.id IS NULL, c.id""",
"known unmatched": """SELECT c.id FROM candidates AS c
WHERE c.id IS NOT NULL
AND NOT EXISTS (SELECT 1 FROM blocked AS b WHERE b.id = c.id)
ORDER BY c.id""",
}
for label, query in queries.items():
print(label, db.execute(query).fetchall())
db.execute("DELETE FROM blocked")
print("empty right side", db.execute("""SELECT id FROM candidates
WHERE id NOT IN (SELECT id FROM blocked)
ORDER BY id IS NULL, id""").fetchall())
finally:
db.close()Run:
python3 exclude-null-keys.pyObserved output:
NOT IN []
NOT EXISTS [(2,), (None,)]
known unmatched [(2,)]
empty right side [(1,), (2,), (None,)]Choose the missing-ID policy
For candidate 2, the blocked list contains no match but does contain NULL. Its NOT IN result is NULL, so the WHERE clause discards it. Candidate 1 is a definite match and is excluded too. SQLite’s IN/NOT IN truth table specifies these cases.
The correlated NOT EXISTS query asks whether an equal blocked ID exists. A NULL blocked ID creates no equality match. This keeps candidate 2, but also keeps the missing candidate ID: equality with NULL does not produce a matching row. The final exclusion query adds c.id IS NOT NULL because this report accepts only known IDs.
Filtering NULLs out of the right-hand subquery is another possible policy. It does not decide how to handle a NULL on the left. The final output also tests an empty right side: SQLite returns true for NOT IN in that case, even for a NULL left operand.
Use non-null identifiers where the data model requires them. If missing IDs are legitimate, document whether the report includes them. Do not replace one query form with the other without testing that decision.
Clean up
In the same terminal, remove only the example file and the empty scratch directory:
rm -- exclude-null-keys.py
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dirAcknowledgements
This post was created with assistance from AI.