Product case study · November 17, 2026
Ask Claude Code why one NULL erased your SQL report
Debug a SQLite missing-customer query with Claude Code. Reproduce the NULL trap, define the matching rule and verify the repair against six local cases.

Give the agent expected customer IDs before asking for a query rewrite. An empty report can be a logic bug even when SQL runs without errors.
Your report says every customer has placed an order. The query ran without an error. The problem is one order whose customer ID is unknown: a single NULL can make a NOT IN filter hide all the customers you meant to find.
This is a good job for a coding agent because you can make the wrong answer obvious with three customers. Give Claude Code those rows and the answer you expect before asking it to rewrite SQL. Otherwise, a tidy query can become a very convincing explanation of the wrong result.
Use Bash on Linux, macOS or WSL, Python 3 with sqlite3, and an installed, signed-in Claude Code. The data is fictional. The baseline and reference SQL below were checked locally; the agent prompt is an authored exercise, not a recorded Claude run. Official documentation was checked November 17, 2026.
Make the empty report reproducible
Create a disposable folder and the faulty query:
sql_lab=$(mktemp -d /tmp/claude-sql-null.XXXXXX)
cd "$sql_lab"
cat > missing_customers.sql <<'SQL'
SELECT id
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders)
ORDER BY id;
SQL
Save this as check_query.py in the same folder:
from pathlib import Path
import sqlite3
import sys
query = Path("missing_customers.sql").read_text(encoding="utf-8")
cases = [
("one known order", [1], [2, 3]),
("known plus unknown", [1, None], [2, 3]),
("only unknown", [None], [1, 2, 3]),
("no orders", [], [1, 2, 3]),
("duplicate known orders", [1, 1, None], [2, 3]),
("everyone ordered", [1, 2, 3, None], []),
]
failures = 0
for label, order_customers, expected in cases:
db = sqlite3.connect(":memory:")
try:
db.executescript("""
CREATE TABLE customers (id INTEGER PRIMARY KEY);
CREATE TABLE orders (customer_id INTEGER);
INSERT INTO customers VALUES (1), (2), (3);
""")
db.executemany(
"INSERT INTO orders (customer_id) VALUES (?)",
[(value,) for value in order_customers],
)
actual = [row[0] for row in db.execute(query)]
passed = actual == expected
failures += int(not passed)
print(f"{'PASS' if passed else 'FAIL'} {label}: "
f"got {actual}; expected {expected}")
finally:
db.close()
print(f"{len(cases) - failures}/{len(cases)} cases passed")
sys.exit(1 if failures else 0)
Run it:
python3 check_query.py
The baseline passes three cases and fails three. In known plus unknown, it returns [] instead of [2, 3]. It also fails only unknown and duplicate known orders. Keep the cases that already pass: they guard against a repair that simply returns every customer.
The runner uses a fresh in-memory SQLite database for each case and closes it afterward. Python None supplies SQL NULL. No database server or real customer export is needed.
Why the missing rows disappear
For customer 2, the question becomes 2 NOT IN (1, NULL). The result is NULL, not true: the unknown value prevents the comparison from establishing non-membership. SQLite documents this in its IN and NOT IN result matrix.
A WHERE clause retains rows whose condition is true; false and NULL both exclude the row. Customer 1 is excluded because it has a match. Customers 2 and 3 are excluded because their condition is unknown. The empty output looks clean, but it answers the wrong question.
Our business rule is narrower: an order with no customer ID does not establish that any particular customer ordered. That rule needs to be explicit before the agent chooses a replacement.
Give Claude the matching rule, not a preferred keyword
Save SPEC.md:
Return the IDs of customers with no order whose customer_id equals their id.
Customer IDs are non-null integers. An order customer_id may be NULL.
A NULL order customer_id matches no customer.
Return each qualifying customer once, ordered by id ascending.
Do not remove or modify order records, including unknown-customer orders.
Edit only missing_customers.sql. Keep check_query.py and this contract unchanged.
The deliverable is one SELECT statement for SQLite, with no dependencies.
Start claude in that folder. Confirm its working directory and review its active tool permissions, then paste:
Read SPEC.md, missing_customers.sql and check_query.py.
Run python3 check_query.py and report which cases fail.
Explain why the current query loses customers when an order has NULL.
Repair only missing_customers.sql to satisfy the contract.
Do not change tests, delete fixture rows, install packages, access a real
database, commit or delegate work.
Run the same check again. Report the actual IDs and exit status.
If you cannot execute it, say so. Explain why your query keeps customers
without a matching order when unknown-customer orders are present.
Anthropic’s debugging workflow recommends supplying reproduction steps and the command that exposes the problem. Here, the fixture is more valuable than a screenshot of an empty table: the agent can rerun it after every edit. The prompt sets scope; your tool permissions determine what the session can actually access.
Review the reference answer
A compact repair is:
SELECT c.id
FROM customers AS c
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.customer_id = c.id
)
ORDER BY c.id;
The inner query asks whether a matching order exists for the current customer. EXISTS tests whether the subquery returns any rows. Negating it keeps customers with no match. The equality condition inside the subquery is essential; omitting it would ask whether any order exists at all.
Exit Claude, read missing_customers.sql, then run python3 check_query.py yourself. The reference query passed all six fixtures locally with exit status 0. That verifies this query on these inputs; it does not establish what a future agent session will produce.
For this contract, keeping NOT IN and excluding NULL inside its subquery is also valid:
SELECT id
FROM customers
WHERE id NOT IN (
SELECT customer_id FROM orders WHERE customer_id IS NOT NULL
)
ORDER BY id;
That alternative also passed the six local checks. I prefer NOT EXISTS here because “no matching order” reads directly as the requirement. This is a readability preference, not a claim that one form always runs faster.
Catch the repairs that change the question
Do not accept a proposal to delete unknown-customer orders. Those records may still matter; the task is to fix a report. Likewise, replacing NULL with an invented sentinel ID adds a new assumption about which IDs are possible.
If Claude removes ORDER BY, ask it to restore the requested ordering. If it edits the fixture to remove None, restore the original fixture and rerun: the awkward input is the point of the exercise. A FileNotFoundError usually means you ran the checker outside the folder containing the SQL file. If importing sqlite3 fails, use your Python distributor’s instructions for that optional module before continuing.
Before applying this pattern elsewhere, write down what “missing” means there. Should cancelled orders count? Can the outer identifier also be null? Which database engine will run it? Add cases for those decisions and check performance on representative data separately. Six tiny fixtures settle the logic of this exercise, not the cost of a production query.
The useful habit is to put the expected IDs beside the SQL. “It ran” is a weak stopping point for an agent working on reports; “these are exactly the rows we asked for” is a much better one.
Sources
Checked November 17, 2026:
- SQLite: SQL expressions —
NOT IN,NULLandEXISTSsemantics. - SQLite: SELECT — filtering and result ordering.
- Python: sqlite3 — in-memory databases, bound parameters and Python-to-SQLite types.
- Anthropic: Common workflows — launching Claude Code and supplying reproducible debugging tasks.
Put this to work
How to distinguish a valid SQL statement from a correct business answer.
Try
Ask Claude Code to repair one SQL file in a disposable practice folder.
Prove it worked
Run the same six fixtures before and after the edit and compare exact IDs.
Keep in view
- Reproduce an empty report caused by a NULL in a subquery.
- Give Claude Code a fixed contract for customers without matching orders.
- Check empty, duplicate and unknown-customer order data locally.