AI-Written SQL: A Missing ID Can Hide Every Row

An AI-written work queue can return no rows, show no error and still leave records unmatched. One trap appears when a query asks which IDs are absent from another list. With the exclusion pattern below, a missing ID in that comparison list can prevent every candidate from appearing.

For an independent creator or business owner, an empty report needs an explanation before it becomes a decision that the work is complete. Establish the meaning of the query, then test a small example containing incomplete data. The difference between no matching record and confirmed completion matters.

Define the question before generating the query

Imagine a fictional image studio checking which assets have no linked completed-review record. Its assets table contains three unique, non-null IDs: A101, A102 and A103. Its reviews table contains two imported records: one with asset ID A102 and another whose asset ID is NULL.

For this fixture, the missing value means that the second review cannot yet be linked to an asset. The requested report should identify A101 and A103 as having no matching review record, while keeping the unassigned review visible for reconciliation. It cannot establish that those assets were definitely never reviewed.

See why the exclusion can return nothing

An assistant might propose this query:

SELECT asset_id FROM assets WHERE asset_id NOT IN (SELECT asset_id FROM reviews);

PostgreSQL’s subquery-expression documentation explains the important qualification: when no equal value is found but the comparison contains a null value, NOT IN evaluates to null rather than true. A match, meanwhile, makes the exclusion false.

The PostgreSQL guidance on WHERE states that a row is retained only when the condition is true. Both false and null results are discarded. Unknown therefore does not pass this filter.

In the fictional example, A102 is excluded because it matches a review. A101 and A103 are not positively established as outside a list that includes an unknown ID. The original query returns no rows when executed on this fixture in SQLite. That output does not mean all three assets have linked reviews.

SQLite’s expression documentation records this behaviour in its IN/NOT IN truth table. It also distinguishes an empty comparison set from one containing NULL. An empty review table and a table containing an unassigned review are different test cases.

Ask whether a matching record exists

For the stated question, a correlated NOT EXISTS query expresses the matching test directly. PostgreSQL documents EXISTS as checking whether a subquery returns any rows. Here the inner query looks for a review with the current asset’s ID:

SELECT a.asset_id FROM assets AS a WHERE NOT EXISTS (SELECT 1 FROM reviews AS r WHERE r.asset_id = a.asset_id);

On the same executed fixture, this returns A101 and A103. The unassigned review does not satisfy the equality condition for either known ID. The output has the intended meaning: these assets have no matching review record under this key.

The candidate IDs are deliberately non-null in this example. If your candidate records can also lack IDs, decide how those records should be classified and reported separately. Do not assume that changing the exclusion syntax settles the business meaning of every incomplete record.

Filtering the list is another deliberate choice

A second formulation removes null IDs from the comparison subquery:

SELECT asset_id FROM assets WHERE asset_id NOT IN (SELECT asset_id FROM reviews WHERE asset_id IS NOT NULL);

This also returns A101 and A103 for the fixture. It is appropriate only with a clear account of what happens to excluded incomplete records. Keep a separate count or review list of the unassigned entries. A working exclusion query has not repaired their missing links.

Avoid replacing the missing ID with a made-up identifier simply to obtain a more reassuring result. First decide whether the record needs correction, a link recovered from its source, or an explicit unresolved status. The report should preserve that decision rather than conceal it inside a convenient expression.

Keep a small set of expected results

The three query forms were checked against six fictional fixtures using SQLite 3.53.1. For the intended no-matching-review rule, the expected outputs were:

• No review records: A101, A102 and A103.
• Only a review for A102: A101 and A103.
• A102 plus an unassigned review: A101 and A103, with the unassigned record separately flagged.
• Only an unassigned review: all three asset IDs, with that review still unresolved.
• One linked review for each asset: no asset IDs.
• Two reviews linked to A102: A101 and A103.

The original unfiltered NOT IN query returned no rows in the two fixtures containing a missing review ID. The matching NOT EXISTS query and filtered-list version produced the expected ID sets in all six cases. These checks establish behaviour for these fixtures; they do not verify a production report with additional conditions.

Keep the expected IDs alongside the query. A total count alone can hide a wrong selection of records. The earlier MaryChuks guide to counting each order once in AI-written SQL addresses a separate relationship problem: repeated rows introduced by joins. Both checks can matter in a larger report.

Make missing-ID handling part of the handover

Where every completed review must identify its asset, consider enforcing that rule at the data boundary. PostgreSQL’s constraint documentation distinguishes a NOT NULL requirement from a foreign key: a referencing row can normally contain null without satisfying the foreign-key match. The requirements need to be specified together when both are intended.

That is a proposed design decision, not an instruction to discard incomplete imports. A staging area can retain unresolved records for investigation before they enter the completed-review table. Agree what that separation means and how exceptions remain visible.

A useful AI brief could request: “Prepare a read-only query for assets with no review matching their asset ID. Asset IDs are unique and non-null; imported review IDs may be null. Explain the missing-value handling, show the expected IDs for these fixtures, and identify unassigned reviews separately.”

Review the generated SQL in the database system actually used by the business. Then examine the selected IDs and the unresolved records together. An empty work queue becomes meaningful when you can explain why each record was included, excluded or left awaiting reconciliation.

Sources

• PostgreSQL: Subquery Expressions.
• PostgreSQL: the WHERE clause.
• SQLite: IN and NOT IN expressions.
• PostgreSQL: Constraints.

Documentation checked and example queries executed on 26 October 2026. The studio, data and suggested AI brief are illustrative.