The export is empty, but the query succeeded

A reporting agent receives a list of customer IDs to exclude from an operational report. It writes a NOT IN filter. One row in the exclusion list has a missing ID. The query runs without a syntax error, yet expected customers disappear.

An empty result can be valid, but it is not self-explanatory. Before an agent labels it no eligible records, require it to explain how missing values affect the exclusion rule.

This article is for people reviewing agent-written PostgreSQL reporting queries. It is not a recipe for bypassing suppression, consent or access-control rules.

Methodology: compare truth states, not query length

We checked PostgreSQL documentation for subquery expressions, comparison operators and table expressions on September 30, 2026. The example uses invented IDs and documentation-based reasoning; we did not execute it against a database or inspect private customer data.

The selection criteria are the intended eligible population, the meaning of a missing key on either side, and the evidence needed to explain exclusions. We do not claim one SQL form is always faster.

Our join-cardinality guide covers multiplication during enrichment. Here the problem is a filtering decision before the report is summarized.

Work through one small list

Imagine candidates A and B, and an exclusion list containing B plus a null value.

For B, the list contains an equal value, so NOT IN does not retain it. For A, there is no equal known value, but the null prevents the comparison from establishing that A differs from every member. The result is unknown rather than true.

The subquery documentation describes this NOT IN behavior and distinguishes it from EXISTS, which tests whether a subquery returns any rows.

The table-expression documentation explains the next step: a WHERE condition retains rows only when its result is true. False and null results are both discarded.

Thus neither A nor B survives this invented filter. That conclusion is a worked example of documented semantics, not evidence of an incident on OpenAgentSkill.

Fix the business rule before rewriting the operator

A common candidate replacement is a correlated NOT EXISTS check looking for an exclusion row whose ID equals the candidate ID. That asks whether a matching row exists, rather than comparing the candidate against every value in the list.

But it still needs a missing-identity policy. A candidate with a null ID will not match an exclusion through ordinary equality, so a naive NOT EXISTS filter can retain it. That may be unacceptable for the task.

PostgreSQL's comparison documentation explains that ordinary comparisons involving null produce unknown. It also documents IS NULL and null-aware comparison predicates. Those mechanisms do not choose the business meaning for you.

For our reporting example, a conservative proposed policy is to send unknown candidate IDs to a separate review group, exclude confirmed matches, and retain known non-matches only after validating the exclusion source. Different products may need a different approved policy.

Do not silently remove nulls from a suppression list merely because doing so restores a plausible row count.

Give each record a disposition

Ask the agent for three separate outputs: included known identities, confirmed exclusions, and unresolved identities or source defects. Preserve reasons rather than burying them in a single filter.

For the A/B example, the unresolved exclusion entry must be explained. Is it an incomplete export, a corrupt record or a row whose identifier belongs in another field? Until that is known, the agent cannot establish that the exclusion list is complete.

For a low-stakes analytical preview, the owner may accept a clearly caveated result. For a workflow that sends messages or grants access, uncertainty can require stopping the action. A report-generation request alone does not authorize either downstream operation.

Record the exclusion source, its retrieval time, expected scope and approved treatment of missing values. The query text is only one part of the decision.

Review a matrix before accepting the rewrite

Use disposable data and write expected outcomes before running either query. Include:

A known candidate with no matching exclusion.
A known candidate with one matching exclusion.
A duplicate exclusion for the same known ID.
A null exclusion entry.
A candidate with a null ID.
An empty exclusion list.

Add a case where both sides have null IDs. Decide whether these should be treated as the same identity, as unrelated unknowns, or as an unresolved condition. Do not let a convenient operator settle identity policy accidentally.

If using a left-join-based exclusion instead, choose a reliable matched-row indicator and review the join predicate. A nullable descriptive field is not a trustworthy substitute for evidence that a match occurred.

This matrix is proposed verification work, not a passed test suite. The acceptance record should name the database version, actual query and observed dispositions when someone executes it.

Limitations and the reporting handoff

Null handling is not the only source of exclusion errors. Incorrect key normalization, stale lists, mixed identifier systems and time-dependent eligibility can all produce a logically consistent but wrong report.

This article does not establish completeness of an upstream export or appropriate consent rules. Performance also depends on schema, indexes, data distribution and the actual plan; do not promote an operator rewrite as a measured optimization without evidence.

Hand off population counts with unresolved counts and reasons, plus the approved missing-key policy. If you cannot explain why the export is empty, deliver a diagnostic instead of a confident business conclusion.

The skill directory can help locate reporting workflows. Before adopting one, ask whether it preserves uncertainty or converts it into an apparently finished answer.