Postgres NOT IN with NULL: choose the missing-row rule

Postgres NOT IN can return no rows when its exclusion set contains NULL: an unmatched comparison can evaluate to unknown, and WHERE keeps only true results. Define what a missing key means before rewriting the query. A correlated NOT EXISTS often expresses the intended anti-join, but ordinary equality and null-safe equality produce different answers. ^[1][2][3]

What does “not suppressed” mean for a missing key?#

Write down which account identifiers should be exported when either side has a missing reference. A report key that is unknown might mean “not eligible for export,” “eligible unless a matching known key is suppressed,” or “matches a suppression whose key is also missing.” SQL can implement each rule, but it cannot choose the right one for the product.

Scenario: an illustrative account export selects records whose external reference is absent from a suppression table. Some accounts have not yet received an external reference. A recent import also put a missing reference into the suppression table. The export suddenly becomes empty, and the first response is to replace NOT IN with NOT EXISTS.

That replacement might restore rows while silently including accounts the product intended to exclude. Before fixing the query, write the expected account identifiers for a small fixture. Include a matching known reference, an unmatched known reference, a missing account reference and a missing suppression reference. Decide what each should do without looking at the query's current output.

Keep that expected set beside the repaired query. It is more useful than a rule saying to always prefer one SQL spelling. A faster plan still exports the wrong accounts if the absence rule is wrong. In an export workflow, the consequence may be sending records that should be held, rather than merely making a dashboard count inaccurate.

Why can NULL remove the unmatched rows?#

Ordinary SQL comparisons involving a null operand yield unknown. When a nonmatching known key is tested against an exclusion set containing a null, the database cannot establish that it differs from every member. The NOT IN result is therefore null in that case. WHERE discards it because the condition is not true. ^[1][2][3]

Be precise about the boundary: this does not mean the expression always returns false. A matching value can make NOT IN false, while an unmatched value facing a null member can produce unknown. Those results both disappear from a WHERE result, which can hide the distinction until you inspect the Boolean expression directly. ^[1][3]

Run the following fixture in a disposable session. Its identifiers and data are illustrative. Temporary tables keep the example away from application schemas, and the final rollback discards the transaction's changes. Keep the session open until the later queries have been run.

BEGIN;
CREATE TEMP TABLE accounts_demo (
  id integer PRIMARY KEY,
  tenant text NOT NULL,
  external_ref text
);
CREATE TEMP TABLE suppression_demo (
  tenant text NOT NULL,
  external_ref text
);
INSERT INTO accounts_demo VALUES
  (1, 'blue', 'A'),
  (2, 'blue', 'B'),
  (3, 'blue', NULL),
  (4, 'green', 'B');
INSERT INTO suppression_demo VALUES
  ('blue', 'A'),
  ('blue', NULL),
  ('green', 'B');

SELECT id,
       external_ref NOT IN (
         SELECT external_ref FROM suppression_demo
         WHERE tenant = 'blue'
       ) AS eligible
FROM accounts_demo
WHERE tenant = 'blue'
ORDER BY id;

Scenario: the expected Boolean results for the blue tenant are false for account 1 and null for accounts 2 and 3. Adding the expression as a WHERE condition therefore returns no account identifiers. Use your SQL client's explicit null display so an empty-looking Boolean cell is not mistaken for false.

Inspecting the expression in the select list is a useful diagnostic device. It shows the intermediate value that the filter normally hides. Keep the fixture small enough to reason about manually. A production query with many joins can otherwise make a null-sensitive predicate look like an unrelated missing-data problem.

Which rewrite matches the intended rule?#

Use correlated NOT EXISTS with ordinary equality when you mean “there is no row with an equal known reference in this tenant.” An equality comparison against a null reference does not match, so an account with a null reference can remain in the result. That outcome must be intentional. ^[1][2]

SELECT a.id
FROM accounts_demo AS a
WHERE a.tenant = 'blue'
  AND NOT EXISTS (
    SELECT 1
    FROM suppression_demo AS s
    WHERE s.tenant = a.tenant
      AND s.external_ref = a.external_ref
  )
ORDER BY a.id;

Scenario: this query should return accounts 2 and 3. It excludes the known matching reference and retains both the known unmatched account and the missing-reference account. The green tenant's suppression does not remove the blue tenant's account because tenant identity is part of the correlation.

If missing account references are ineligible for export, add a.external_ref IS NOT NULL to the outer filter. That should leave account 2. This explicitly encodes the product requirement. Removing null values only from the subquery is a different rewrite: it addresses null members in the exclusion set but does not independently document the outer-key policy. ^[1][2]

If the business deliberately treats missing references as equal, use IS NOT DISTINCT FROM inside the correlation. PostgreSQL documents that this predicate treats two null inputs as equal. In this fixture, the blue tenant's null suppression then excludes account 3, again leaving account 2. The identical result here comes from a different rule and will diverge on other fixtures. ^[2]

SELECT a.id
FROM accounts_demo AS a
WHERE a.tenant = 'blue'
  AND NOT EXISTS (
    SELECT 1
    FROM suppression_demo AS s
    WHERE s.tenant = a.tenant
      AND s.external_ref IS NOT DISTINCT FROM a.external_ref
  )
ORDER BY a.id;

Do not replace null with a magic string merely to make equality work. The sentinel becomes another domain value that must never collide with real data. A null-safe predicate states the intended comparison directly. If a missing value is actually invalid in the domain, a validated data repair and a suitable constraint may be the better long-term design.

What changes when the exclusion set is empty?#

An empty exclusion set exposes rules that can look equivalent on the first fixture. PostgreSQL defines NOT IN as true when its subquery has no rows. A null outer key against that empty set is therefore different from a null key against a nonempty set. Include this boundary in the contract tests. ^[1]

For a controlled test, delete only the blue tenant's temporary suppression rows and rerun the candidate queries. A NOT EXISTS query using ordinary equality returns all blue accounts, including the missing reference. A version with an explicit outer IS NOT NULL filter still excludes the missing-reference account. The null-safe version returns it now because no null suppression exists.

This is why a single expected output is insufficient. The outer-null exclusion rule and the null-equals-null suppression rule both returned account 2 in the original fixture. After removing the suppressions, they disagree. A useful test set distinguishes the meaning of the rules, rather than merely checking that the first empty export is no longer empty.

Duplicate suppression keys should not duplicate an account in a NOT EXISTS result, because the predicate asks whether any matching row exists. A join-based rewrite needs its own review of multiplicity and the chosen right-side null test. Check exact account identifiers and their counts, not just whether a query returned something. ^[1][3]

How do you preserve tenant boundaries and performance?#

Keep tenant identity inside the correlated match whenever the key's uniqueness is tenant-scoped. An outer tenant filter limits which accounts are being exported; it does not automatically limit which suppression rows are considered. Omitting the inner correlation can make another tenant's reference suppress the current tenant's account.

The fixture intentionally includes the same reference in another tenant to expose that mistake. Retain a similar regression case even if row-level security also applies. Query semantics and database authorization answer related but separate questions. The Postgres row-level security guide discusses the authorization layer; it does not replace an explicit query result contract.

Once the result is correct, inspect the plan on representative data and the actual supported PostgreSQL version. The current documentation retrieved for this article identifies itself as PostgreSQL 18. Older comparisons of NOT IN, NOT EXISTS and joins can be useful explanations, but their historical plan shapes and timings are not current performance guarantees. ^[4]

Use plain EXPLAIN to inspect a plan without executing the query. EXPLAIN ANALYZE actually runs the statement, so review its operational impact before applying it to an expensive query or a statement with side effects. Benchmark alternatives with realistic row counts, null distributions, statistics and indexing rather than copying a published timing. ^[4]

For the equality-based correlation, a tenant-and-reference index is a candidate to evaluate, not a promised best plan. Null-safe comparisons may produce different plans. Measure the exact query you intend to deploy and retain the expected-set tests alongside the plan evidence. A faster query that changes which accounts appear is a different product behavior.

What does a reviewable fix contain?#

A reviewable fix includes the chosen business rule, the repaired SQL and exact expected identifiers across the boundary fixtures. Record whether outer nulls are eligible and whether null equals null for matching. Those decisions should be readable without running the database or remembering the incident that motivated the change.

Run cases with an empty suppression set, only a null suppression, only known suppressions, duplicate suppressions and the same reference in another tenant. Compare sets in both directions so an unexpected extra row fails just as clearly as a missing row. Include multiplicity checks if the downstream export can duplicate work.

Keep the fixture separate from real export side effects. Running a SELECT in a disposable transaction checks result semantics, while executing an export job can send files, notifications or downstream actions that rollback cannot undo. Validate the selection first, then rehearse the delivery workflow with its own harmless fixture and acceptance criteria.

When the temporary example is complete, run ROLLBACK;. The SQL here is a reproducible fixture, not a claim that a production export was repaired or benchmarked. Validate the real schema, transaction snapshot and deployment path before release. If the output is paginated, verify the separate cursor precision contract as well; correcting a filter does not establish correct page boundaries.

Frequently asked questions#

Does NOT IN containing NULL always return false?

No. A matching known value can produce false, while an unmatched value can produce null when the exclusion set contains null. In a WHERE filter, both are discarded. Inspect the Boolean result directly when diagnosing the difference. ^[1][3]

Is NOT EXISTS always equivalent to NOT IN?

No. Null semantics differ. Ordinary equality inside a correlated NOT EXISTS does not match null values, so a missing outer key can remain eligible. Define and test the intended missing-key rule before treating the rewrite as behavior-preserving. ^[1][2]

Should I filter nulls only inside the subquery?

Only when that expresses the intended rule. Removing null exclusions addresses one source of unknown comparisons, but it does not independently define whether a missing outer key should be exported. Test outer nulls and empty exclusion sets separately. ^[1][2]

Is NOT EXISTS guaranteed to run faster?

No. Performance depends on the query, data, indexes, statistics and database version. Establish the correct result first, then inspect and measure the actual plan. Historical benchmark results are not evidence of your current query's behavior. ^[4]