Give SQL exclusion queries an explicit NULL policy
In short: Test both sides of a SQL exclusion query: keep a NULL in processed IDs from hiding valid work, while rejecting unidentified incoming rows.
sql ▸ WITH incoming(id) AS (VALUES (1), (2), (3), (NULL)),
processed(id) AS (VALUES (2), (NULL))
SELECT i.id
FROM incoming AS i
WHERE i.id IS NOT NULL
AND NOT EXISTS (
SELECT 1 FROM processed AS p WHERE p.id = i.id
)
ORDER BY i.id;Three known IDs went in. One was already processed. The query returned nothing. In a synthetic SQLite 3.53.2 check on 13 October 2026, a single NULL in the processed-ID list was enough to hide the two remaining IDs from a NOT IN query.
Give SQL exclusion queries an explicit NULL policy on both sides. For this example, the rule is: return known incoming IDs with no matching processed record; leave unidentified incoming rows out. A correlated NOT EXISTS handles the exclusion, while i.id IS NOT NULL makes the input policy visible. Neither clause substitutes for the other.
Why SQLite NOT IN can return no candidates
SQLite's expression reference documents the awkward case: when an ID has no match but the exclusion set contains NULL, NOT IN can evaluate to NULL, not true. An ID that does match is excluded normally. Together, those outcomes can remove every candidate.
The SELECT reference explains the final step: WHERE keeps only true expressions. Both false and NULL are filtered out. This is not SQLite reporting that every item was processed; it is the query failing to establish the required condition.
That distinction matters when an empty result is interpreted as “nothing left to do”. The worker can look perfectly healthy while its selection rule hides the backlog. That is a plausible application consequence, not a production incident measured here.
Give SQL exclusion queries a NULL policy in code
Run this self-contained statement in SQLite. The two common table expressions provide disposable input rows; they do not create tables or touch an existing queue. Expected output: 1, then 3.
WITH incoming(id) AS (VALUES (1), (2), (3), (NULL)),
processed(id) AS (VALUES (2), (NULL))
SELECT i.id
FROM incoming AS i
WHERE i.id IS NOT NULL
AND NOT EXISTS (
SELECT 1 FROM processed AS p WHERE p.id = i.id
)
ORDER BY i.id;
The inner query asks whether a matching processed row exists. SQLite's EXISTS rules depend on whether the subquery returns rows, not the values selected by those rows. Here, the equality predicate defines the match. The literal 1 is just a conventional projection.
I executed this exact statement through Python's sqlite3 binding, against an in-memory database. Removing the outer non-NULL guard also returned the unidentified incoming row. Changing operators fixed one failure but left a separate decision unresolved.
Test the empty exclusion set as well
The input stayed 1, 2, 3, NULL throughout these local checks. “Guarded” means the complete query above, including its input-ID check.
| Processed IDs | NOT IN result | Guarded NOT EXISTS result |
|---|---|---|
| Empty | NULL, 1, 2, 3 | 1, 2, 3 |
| 2 | 1, 3 | 1, 3 |
| 2, NULL | No rows | 1, 3 |
| NULL | No rows | 1, 2, 3 |
| 2, 2, NULL | No rows | 1, 3 |
The empty-set case is worth keeping in the regression suite. With no processed rows, the unguarded NOT IN admitted the missing input ID. A fixture containing only valid, matching IDs would miss both surprises.
These were functional checks with small synthetic integer inputs. They establish the returned rows, not query speed, an optimal index or behaviour in another database engine.
My judgement for a document-ingestion queue
For a hypothetical document-ingestion worker, I would treat an unidentified row as a data-quality task, not as ordinary unprocessed work. The selection query should exclude it, and a separate check should count or quarantine it. Quietly excluding bad inputs forever would merely move the blind spot.
My review question would be: “What happens when either side has no usable identity?” That is more useful than a blanket rule to replace every NOT IN. If the schema guarantees non-NULL identifiers, the trade-off is different; inspect that guarantee before rewriting working queries.
This example only selects candidates. It does not claim them atomically, prevent two workers selecting the same item, or roll back downstream side effects. Those require their own transaction and ownership design. Nor does it deduplicate repeated incoming rows.
What's in it for you
- Add empty, NULL-only and mixed exclusion sets to query tests before trusting “zero pending”.
- Make missing input identity a visible policy, with a separate route for repair.
An exclusion query needs to say what is absent—and what was never identified.
Sources
- SQLite — SQL language expressions, IN/NOT IN and EXISTS reference, accessed 13 October 2026.
- SQLite — SELECT, WHERE filtering reference, accessed 13 October 2026.
