The direct answer
Define the absence predicate and its input contract before interpreting a discrepancy report. A null-sensitive NOT IN condition can be unknown, while WHERE retains only true conditions. Require complete, authorized input sets, independently established non-null scalar keys and the exact reviewed predicate. An empty captured report alone cannot establish that every candidate has a counterpart.
An empty discrepancy report can hide a broken assumption
A fictional AI assistant summarizes a report of permitted catalogue records missing from a completed-reference set. The service wants a useful empty answer when everything is accounted for. It must distinguish that outcome from a filter that silently suppresses candidates because the comparison data violated its contract.
This worksheet selects a separate fixed, read-only PostgreSQL 18 report over ordinary scalar integer keys. Trusted application code owns both input scopes, the comparison and the output fields. It supplies no generated SQL or executable replacement query. All paper cases are PROPOSED_UNRUN; no database, statement, row fixture or comparison runtime was constructed.
Read membership and filtering as separate stages
PostgreSQL 18’s subquery chapter defines scalar NOT IN as false when an equal value exists and true when all comparisons are unequal, including an empty comparison set. With no equal value and a null comparison value, its result is null rather than true.
The table-expression chapter says WHERE keeps a row when its condition is true and discards it when false or null. Thus the same filtered absence of a candidate can conceal different predicate dispositions. Calling the null result “unknown” here describes its logical role; it is not a third business identity.
The chapter labels establish documentation version 18, not an inspected server release. This profile excludes composite keys, custom equality operators, generated expressions and alternative query engines. Their behavior must not be guessed from this selected scalar discussion.
Keep the paper dispositions visible
Invent candidate references K-A and K-B for distinct, non-null permitted keys, with report revision R-A. These labels name teaching cases, not stored records. The comparison set in each row is stated completely for that case. The table derives only the selected predicate and filter consequence, not an executed report or a true business inventory.
| Candidate / complete comparison case | NOT IN | WHERE retains candidate? |
|---|---|---|
| K-A / only K-B | True | Yes |
| K-B / only K-B | False | No |
| K-A / K-B and null | Unknown | No |
| K-B / K-B and null | False | No |
| K-A / empty set | True | Yes |
The contrasting null-containing cases matter: a null does not make every candidate’s result unknown. For the proposed service, however, either null-containing input violates the selected key contract. The service must hold that report rather than present a reassuring “nothing missing” summary.
A genuinely empty comparison set also needs independent coverage evidence. A failed extraction, hidden source rows or incomplete capture cannot borrow the empty-set row of this paper table.
Reject an invalid input contract without silently changing the report
Our application policy requires both complete inputs to have admitted non-null keys in their declared representation before this report becomes eligible. A null candidate key is a contract failure too. Keep its disposition in a permitted minimized diagnostic channel; do not invent an identifier, substitute a shared sentinel or silently drop the record.
This is stronger than noticing that a filtered result has zero rows. The responsible adapter needs evidence about the complete admitted source sets, not a guess based on whatever rows survived the chosen filter. A snapshot can stabilize visibility without establishing that the selected sources contain every business record required for this task.
If a later requirement permits missing identities, define a different business relation and review the whole predicate. Do not repair the current plan by textual substitution. The existing enrichment worksheet still owns its own pandas matching and coverage contract.
Review the complete predicate rather than swapping an operator name
The same subquery chapter defines EXISTS through whether its subquery returns any rows. That makes the subquery’s correlation and conditions essential to its meaning. It is not permission to replace every NOT IN occurrence with an unspecified NOT EXISTS expression.
For this report, the reviewer must identify the exact trusted template, ordinary key types, intended comparison, admitted view and output association. A plausible operator name cannot fill in a missing tenant restriction or an incorrectly related source. Keep the existing fixed reporting executor and its resource controls unchanged.
| Receipt cell | Independent evidence required |
|---|---|
| Input association | Complete candidate and comparison revisions, owners and authorized scopes. |
| Identity contract | Supported ordinary scalar representation and complete non-null admission. |
| Predicate | Reviewed fixed template and exact correspondence to the business question. |
| Read view | Supported visibility and coordination for this report. |
| Result capture | Bounded complete output, identities and explicit unavailable/error states. |
| Release | Current row, purpose and recipient rights. |
The ordinary positive admits only the fixed K-A discrepancy plan when every cell is independently established. It neither runs the report nor grants permission to disclose a later result. Retain the input and template association through any eventual summary.
Walk eight proposed review cards
| Condition | Proposed disposition |
|---|---|
| 1. Complete admitted non-null inputs and every receipt cell hold | Conditionally admit the fixed discrepancy plan. |
| 2. A null comparison key is found | Hold the input contract; do not infer no discrepancy from omitted output. |
| 3. A candidate key is null | Record invalid identity; no sentinel or silent removal. |
| 4. An equal comparison value accompanies a null | Preserve the matched case separately while holding the invalid input. |
| 5. The comparison set appears empty | Require independent complete-set evidence before interpreting that case. |
| 6. Someone proposes a generic operator rewrite | Review the full relation, correlation and null policy anew. |
| 7. Source scope, view or result capture is uncertain | Hold the report and its no-discrepancy claim. |
| 8. The computation fits but current release rights fail | Withhold disclosure independently. |
Limit what the worksheet establishes
Actual source invariants, equality behavior, trusted template, established view, complete result adapter, resource controls and current rights are unknown here. Missing evidence blocks a real positive. A read-only label, row count or understood truth table supplies none of those prerequisites.
Boundary is prelaunch. This proposed decision aid is not a shipped SQL validator, report reconciler or authorization service. No query, connection, schema, driver, parser, model or database operation occurred. The existing synthetic policy evidence covers different behavior.
Primary sources
Official references supporting the guidance. Examples in this guide are synthetic and are not reports of a production incident.