Keep NULL separate from an empty string in a query

By Batu · English technical notes

NULL and '' answer different questions. A nullable body can be NULL when no value was supplied, while an empty string can mean that a value was explicitly supplied but contains zero characters. Preserve that distinction in predicates so later code can make an appropriate business decision.

The example inserts one row of each kind plus a non-empty draft. body IS NULL selects only the missing value, and body = '' selects only the deliberately blank value. The assertions verify the expected IDs before the program prints them. In SQLite, ordinary binary operators generally produce NULL when an operand is NULL; IS is the appropriate operator when testing for NULL. SQLite’s expression documentation describes these NULL rules. Python’s sqlite3.connect(':memory:') creates a database that exists only in memory, and Connection.execute() runs the parameterized statements used here. The Python sqlite3 documentation documents both APIs.

Do not replace this pair of predicates with a truthiness test such as WHERE body: SQLite boolean conversion is not a model for distinguishing stored text states. Also decide whether whitespace-only text should be treated as blank; this query intentionally does not trim it. The example uses only standard-library sqlite3; Python 3.6+ is required for its f-string output. AI assistance disclosure: this article was drafted with AI assistance and should be adapted to the application’s data rules.

import sqlite3

con = sqlite3.connect(":memory:")
con.execute("CREATE TABLE note (id INTEGER PRIMARY KEY, body TEXT)")
con.executemany(
    "INSERT INTO note VALUES (?, ?)",
    [(1, None), (2, ""), (3, "draft")],
)

missing = [
    row[0]
    for row in con.execute("SELECT id FROM note WHERE body IS NULL ORDER BY id")
]
blank = [
    row[0]
    for row in con.execute("SELECT id FROM note WHERE body = '' ORDER BY id")
]

assert missing == [1]
assert blank == [2]
print(f"missing={missing}; blank={blank}")
con.close()

missing=[1]; blank=[2]