SQLite Basics: Handle Missing Values with NULL and COALESCE

Missing values need deliberate handling. These examples find absent data, choose a display fallback, and preserve the difference between an unknown amount and zero.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. Each invocation executes SQL in a fresh in-memory database and prints pipe-separated rows, displaying missing values as NULL. These toy databases disappear when the connection closes. Put each statement’s ending semicolon at the end of a line, as shown; this is a teaching runner, not a general SQL shell or CSV exporter.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

1. Find missing contact details

Save as missing-contact.sql:

CREATE TABLE contacts (name TEXT, phone TEXT);
INSERT INTO contacts VALUES ('Ada', NULL), ('Bo', ''), ('Cy', '555-0100');
SELECT name FROM contacts WHERE phone IS NULL ORDER BY name;
SELECT name FROM contacts WHERE phone IS NOT NULL ORDER BY name;

Run:

python3 run-sql.py missing-contact.sql

Output:

Ada
Bo
Cy

IS NULL finds missing values. An empty string is present text, so Bo appears in the second result. Ordinary equality with NULL does not produce true; use IS NULL or IS NOT NULL for these tests.

2. Choose a useful display label

Save as display-label.sql:

CREATE TABLE users (id INTEGER, nickname TEXT, full_name TEXT);
INSERT INTO users VALUES (1, 'Ada', 'Ada Lee'), (2, '', 'Bo Kim'), (3, NULL, NULL);
SELECT id, COALESCE(NULLIF(nickname, ''), full_name, 'Anonymous')
FROM users ORDER BY id;

Run:

python3 run-sql.py display-label.sql

Output:

1|Ada
2|Bo Kim
3|Anonymous

NULLIF converts an empty nickname to NULL. COALESCE returns the first non-NULL choice. This changes the query result without replacing the stored data. Whitespace-only strings need a separate cleaning rule.

3. Make arithmetic defaults explicit

Save as optional-fee.sql:

CREATE TABLE orders (id INTEGER, subtotal INTEGER, fee INTEGER);
INSERT INTO orders VALUES (1, 100, 5), (2, 80, NULL), (3, 60, 0);
SELECT id, subtotal + fee AS raw_total,
       subtotal + COALESCE(fee, 0) AS assumed_total
FROM orders ORDER BY id;

Run:

python3 run-sql.py optional-fee.sql

Output:

1|105|105
2|NULL|80
3|60|60

A missing fee makes the raw total NULL. Defaulting the fee to zero is appropriate only if that is the business rule. Keep the unknown value when a missing fee means the order is not yet priced.

See SQLite’s core function reference and expression reference.

Clean up

In the same terminal, remove only these example files and the empty scratch directory:

rm -- run-sql.py missing-contact.sql display-label.sql optional-fee.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.