Before accepting an AI-written query that selects records “not on a list,” decide what missing identifiers mean on both sides. A NULL in an exclusion subquery can suppress otherwise expected results. Replacing NOT IN with NOT EXISTS can restore those rows while also admitting records whose own identifier is missing. Neither outcome settles whether the business should act.
For developers and report owners, the useful review has two parts: verify the query’s matching behavior, then verify that the source data supports the intended decision. The hypothetical example below separates orders that match a hold, orders with no known match and orders that cannot be classified reliably. Its small SQL fixture was checked locally in SQLite 3.53.1. No AI SQL product or PostgreSQL server was tested.
An empty result can hide an unknown comparison
The PostgreSQL 18 subquery documentation specifies the behavior of NOT IN: when no equal right-hand value exists but at least one right-hand value is null, the result is null rather than true. EXISTS instead asks whether its subquery returns any rows. These are database semantics, not new behaviors introduced by AI.
The PostgreSQL description of the WHERE clause supplies the next part: it retains a row only when its condition is true. A condition that evaluates to false or null discards the row. An empty report therefore need not mean every record positively matched the exclusion list.
Phil Factor’s March 2018 practitioner article demonstrates the NOT IN pitfall with a nullable SQL Server subquery and discusses NOT EXISTS as an alternative. That is historical engineering guidance, not evidence of current assistant failure rates or a performance guarantee for another database.
Our additional recommendation is to review the meaning of the missing records before approving that rewrite for use. A null might represent an optional field, an unresolved import or a failed lookup. Those situations can need different operational responses even when SQL represents them in the same way.
A fictional order-follow-up queue
Imagine a team preparing a list of orders for routine customer follow-up. It should exclude customers on a hold list and route orders with missing customer identifiers for data repair. Other requirements for sending a message remain outside this example.
The orders contain:
| Order | Customer identifier |
|---|---|
| 1 | 101 |
| 2 | 102 |
| 3 | NULL |
The imported hold list contains customer 101 and a second row with a null customer identifier. In this fictional system, that null means an imported hold could not be linked to its customer. The team has not yet established whom it concerns. It could affect customer 102; it could concern someone outside these orders.
A plausible generated query is:
SELECT order_id
FROM orders
WHERE customer_id NOT IN (
SELECT customer_id FROM holds
)
ORDER BY order_id;In the local fixture, it returned no orders. Order 1 matches the known hold. Order 2 has no equal identifier in the list, but the null makes the exclusion condition unknown. Order 3 also fails to produce a true condition against this nonempty list.
The empty output should trigger investigation. It is not evidence that all three orders have confirmed holds, nor that there is simply no work to do. Ask for the selected order identifiers alongside the relevant source records; a total of zero conceals the different reasons for exclusion.
A rewrite can admit a different unknown
Now replace the condition with a correlated NOT EXISTS check:
SELECT o.order_id
FROM orders AS o
WHERE NOT EXISTS (
SELECT 1 FROM holds AS h
WHERE h.customer_id = o.customer_id
)
ORDER BY o.order_id;This returned orders 2 and 3 locally. For order 3, the equality condition finds no matching hold row. The absence of a match does not establish which customer owns that order or whether contact is appropriate.
For the stated rule that missing order identifiers go to repair, explicitly require a known identifier:
SELECT o.order_id
FROM orders AS o
WHERE o.customer_id IS NOT NULL
AND NOT EXISTS (
SELECT 1 FROM holds AS h
WHERE h.customer_id = o.customer_id
)
ORDER BY o.order_id;This returned order 2. It is a candidate among the known identifiers. It is still not an approved follow-up list: the unresolved hold could concern that customer. The query has addressed one matching problem while leaving the source-completeness problem unresolved.
For this fictional workflow, our proposed release rule is to pause automatic follow-up until the unresolved hold is reconciled or an authorized owner supplies an applicable handling decision. A different organization might have a justified narrower response, but the assistant should not invent it by silently discarding null hold records.
Give source defects a visible destination
Keep a separate exception output for orders with missing customer identifiers and for hold entries whose customer could not be identified. Give each exception a reason and an owner. Do not make repairing the main query equivalent to deleting those records from the review.
The distinction can be expressed as an acceptance table:
| Observed condition | What the query establishes | What remains to decide |
|---|---|---|
| Known order key matches a known hold | A matching exclusion exists | Apply the established hold process |
| Known order key has no matching hold | No match was found in the supplied data | Whether the hold source is complete enough to act |
| Order key is missing | The required classification key is unavailable | Repair or use an authorized exception process |
| Hold key is unresolved | The exclusion source has an unidentified entry | Resolve its scope before releasing affected actions |
This is our proposed review method, not a database-enforced business policy. In particular, adding IS NOT NULL inside the hold subquery removes a value from the calculation; it does not establish that removing the corresponding business record was justified.
Avoid using a convenient replacement identifier without a domain rule. Converting every null to zero or an empty string can create matches that did not exist before, or collide with a legitimate stored value. Ask what the missing information means before choosing its representation.
Test an empty list separately from an incomplete one
We checked five fixture states locally. With holds 101 and null, the original NOT IN query returned no orders; unguarded NOT EXISTS returned orders 2 and 3; the known-identifier version returned only order 2.
After removing the unresolved hold, the original query returned order 2, while unguarded NOT EXISTS still included order 3. With a genuinely empty hold table, both unguarded queries returned all three orders, including the order with a null customer identifier. The explicit known-identifier guard retained only orders 1 and 2.
With only a null hold entry, NOT IN again returned no orders, while the guarded version returned orders 1 and 2 as candidates. Duplicating the known hold for customer 101 did not change the selected order identifiers. These checks used fabricated records and assert exact identifiers, not just result counts.
An empty imported table also needs an operational interpretation. It might be a successful import of a truly empty list, or the consequence of a failed extraction. This fixture verifies query behavior for an empty table; it cannot establish which business condition produced that table.
Ask for the decision rule with the generated SQL
The ICLR 2025 Spider 2.0 paper evaluates enterprise text-to-SQL workflows with expected outputs and task-specific evaluation scripts. It motivates checking task results beyond whether a statement executes. Its historical benchmark is not a current model comparison or a measurement of null-handling failures. No benchmark scores are reused here.
For the next exclusion query, give the assistant the key definitions, missing-data meanings and expected dispositions. Request cases for a known match, no match, a missing outer key, an unresolved exclusion key and an empty source. Review the expected answers independently before accepting generated tests.
Our SQL join-fanout guide addresses a separate failure: source facts counted more than once. Here the review concerns records disappearing from or entering a candidate set. Both checks matter, but neither replaces the other.
Retain the query revision, source snapshot or approved test fixture, expected identifiers and unresolved exceptions. Recheck on the target database with its actual types, constraints and permissions. The local SQLite results establish only this small fixture’s behavior. Before connecting the output to an action, make sure “no match found” has not quietly become “known to be allowed.”