Some SQL bugs don't throw errors. The query runs, the tests pass, and everything looks fine until a cleanup job skips records it was supposed to catch.
One of the reasons this happens is NULL inside a NOT IN query.
When NOT IN stops behaving as expected
Imagine you're working on a multi-tenant platform. You have a memberships table and an identity_mappings table. Each mapping connects an external identity to a tenant.
You need to find memberships that don't have a corresponding identity mapping.
A first attempt might look like this:
SELECT m.tenant_id, m.external_id
FROM memberships m
WHERE m.external_id NOT IN (
SELECT i.external_id
FROM identity_mappings i
);
Nothing looks wrong at first glance. You're selecting external IDs that don't exist in the mapping table.
Now suppose the subquery returns these values:
| external_id |
|---|
ext_1042 |
ext_2087 |
NULL |
ext_3911 |
That single NULL can change the result.
SQL uses three-valued logic: a condition can evaluate to TRUE, FALSE, or UNKNOWN. When NULL is involved in a comparison, SQL often can't determine whether the comparison is true or false.
For example:
SELECT 'ext_9001' NOT IN (
'ext_1042',
'ext_2087',
NULL
);
The result isn't TRUE. It's UNKNOWN.
Because a WHERE clause keeps only rows for which the condition evaluates to TRUE, the record gets filtered out.
The query executes successfully. There's no exception to investigate. You simply get fewer rows than expected.
That's what makes this bug easy to miss.
Why NOT EXISTS is often a better fit
One common fix is to replace NOT IN with NOT EXISTS:
SELECT m.tenant_id, m.external_id
FROM memberships m
WHERE NOT EXISTS (
SELECT 1
FROM identity_mappings i
WHERE i.external_id = m.external_id
);
The difference matters.
NOT EXISTS checks whether a matching row exists. An unrelated NULL elsewhere in the subquery doesn't automatically make the condition unknown.
But fixing the NULL problem doesn't necessarily mean the query is correct.
There's another detail in this example that deserves attention.
The multi-tenant bug hiding behind the SQL
In a multi-tenant system, an external ID might be valid in one tenant but completely unrelated to the same value in another tenant.
Suppose the data looks like this:
memberships
| tenant_id | external_id |
|---|---|
tenant_a |
ext_1042 |
tenant_b |
ext_1042 |
identity_mappings
| tenant_id | external_id |
|---|---|
tenant_a |
ext_1042 |
The membership for tenant_b has no matching mapping within its own tenant.
However, this query checks only the external ID:
WHERE NOT EXISTS (
SELECT 1
FROM identity_mappings i
WHERE i.external_id = m.external_id
);
It finds the mapping belonging to tenant_a and treats the tenant_b membership as matched.
The SQL is valid. The logic is wrong.
If the relationship depends on both the tenant and the external ID, the query needs to express both conditions:
SELECT m.tenant_id, m.external_id
FROM memberships m
WHERE NOT EXISTS (
SELECT 1
FROM identity_mappings i
WHERE i.tenant_id = m.tenant_id
AND i.external_id = m.external_id
);
Now the query checks the relationship the application actually cares about.
This is a different class of bug from the original NULL issue. Replacing NOT IN with NOT EXISTS solves one problem, but it doesn't automatically make the underlying relationship correct.
What about NULL in the outer table?
There's one more case worth testing.
What happens if m.external_id itself is NULL?
With the equality condition:
i.external_id = m.external_id
a NULL value doesn't match another NULL value. The subquery finds no matching row, so NOT EXISTS evaluates to TRUE.
That means the membership with a missing external ID can appear in the result.
Whether that's correct depends on the application.
If NULL means the identity hasn't been linked yet, returning that membership might be intentional. If the cleanup job is supposed to process only memberships with known external IDs, you'll need an additional condition:
AND m.external_id IS NOT NULL
Don't add that condition automatically, though. First establish what NULL means in the data model.
A query shouldn't have to guess the business rules.
The tests I'd add before shipping this
For a cleanup job in a multi-tenant system, I'd test these cases explicitly:
- A matching mapping exists for the same tenant and external ID.
- The same external ID exists in another tenant, but not the current one.
- The mapping table contains a
NULLexternal ID. - The membership itself has a
NULLexternal ID. - Duplicate mappings exist for the same tenant and external ID.
I'd also check the database constraints. If each tenant should have at most one mapping for a given external ID, a suitable unique constraint can enforce that rule rather than leaving it entirely to application code.
Before running a destructive cleanup, inspect the candidate rows and verify the expected count. If possible, run the operation inside a transaction and make sure the affected records are exactly the ones you intended to process.
A successful query execution is not proof that the selected rows are correct.
The real lesson
NOT IN and NOT EXISTS are worth understanding, especially when NULL is involved. But the operator isn't always the biggest problem.
A query can handle NULL correctly and still match records across tenant boundaries. It can pass tests built around clean data and fail when production data contains unexpected values. It can be perfectly valid SQL while describing the wrong relationship.
When I review an anti-join, I want to establish three things before worrying about the syntax:
- What exactly makes two records a match?
- What should happen when one of the relevant values is
NULL? - Which columns define the relationship, including tenant boundaries?
Once those rules are clear, choosing the SQL becomes much easier.
Have you ever found a query that was technically correct but was checking the wrong relationship?
Top comments (10)
This one bites everyone exactly once, and the silent part is what makes it nasty. NOT EXISTS is usually my default replacement since it handles NULLs sanely and Postgres can plan it as an anti-join, whereas NOT IN with a subquery can fall back to a much slower hashed subplan. If you do keep NOT IN, a NOT NULL constraint on the subquery column makes the intent explicit and prevents the bug from reappearing later. It's also worth adding a test row with a NULL to the fixtures for exactly this reason.
One thing I'd also check here is whether the anti-join has an index that actually matches the relationship being checked. In a multi-tenant system, an index on
external_idalone might not be enough if every lookup also needs to be scoped bytenant_id.For example, a composite index on
(tenant_id, external_id)can make a big difference when theNOT EXISTSlookup runs against millions of identity mappings. I'd compare both plans withEXPLAIN (ANALYZE, BUFFERS)rather than assuming the rewrite alone fixed the performance issue. A query can be logically correct and still spend most of its time scanning the wrong slice of data.Good catch, thanks Sina. A composite index leading with tenant_id is the right shape here, and column order matters: the equality-scoped column first, then the one used for the anti-join lookup. We'd also check that the plan shows an Anti Join using that index rather than a hash anti join over a large scan. Comparing EXPLAIN (ANALYZE, BUFFERS) before and after is the honest way to prove the rewrite helped.
There's another edge case worth considering when
identity_mappingskeeps historical records instead of physically deleting old mappings.For example, if a tenant can reconnect an external identity after revoking the previous mapping, the anti-join should only consider active records:
I'd consider a partial unique index for that access pattern:
This does two useful things: keeps the index focused on active mappings and prevents duplicate active identities within the same tenant, while still allowing revoked records to remain as history. The important part is making sure the query predicate matches the partial index condition, then checking whether PostgreSQL actually picks it up in the execution plan.
That's another case where the data lifecycle matters just as much as the query itself.
Good addition. A partial unique index on (tenant_id, external_id) WHERE revoked_at IS NULL is a clean way to allow reconnecting an identity while keeping the history rows. As you say, the anti-join predicate has to include revoked_at IS NULL so the planner can use that index, and we'd confirm it with EXPLAIN rather than assume it. It also removes the risk of two active mappings for the same identity slipping in through a race.
One thing I'd be careful with is relying on
NOT EXISTSas the only protection against duplicate mappings. It checks what exists in the current transaction's snapshot, but two workers can still pass that check before either one commits.For example, imagine two OAuth callbacks arriving almost simultaneously for the same tenant and external account. Both transactions see no active mapping, and both attempt to create one. The partial unique index handles the integrity side, but I'd also make the write path explicitly idempotent:
That way, the database becomes the final authority instead of relying on application-level timing. I'd also make sure the application handles the case where the insert does nothing, rather than treating it as a failed connection attempt.
The interesting distinction here is that
NOT EXISTSprotects the read logic, while the unique index and atomic insert protect the write path. Both matter when the same identity can be connected concurrently.Good write-up. One more habit that saved me here: besides rewriting the query, put a per-column null-rate check on the table that feeds the subquery (share of NULLs per day, alert when it moves). NOT IN on a nullable column is only silent until the first NULL arrives, and that usually comes from an upstream change (a new source, a failed join, a default that stopped being filled), not from your query. The check tells you the day it started instead of the day a report came back empty.
That's a good point. I'd actually take it one step further and track it per source, not just per column.
Imagine
identity_mappingsis populated from several identity providers. One provider changes its payload or a LEFT JOIN starts failing, and suddenly 15% of itsexternal_idvalues become NULL. An overall null rate might barely move if the other sources are healthy.Grouping the null rate by
source_systemand ingestion date would make that much easier to catch. I'd also track row volume alongside it, since a drop in incoming records can distort the percentage.That way, you're not just catching the symptom in the query. You're getting closer to identifying which upstream change introduced it.
Boa lembrança do caso do NULL na subconsulta. Um complemento prático: se por algum motivo você precisar manter o NOT IN, filtrar a subconsulta com WHERE external_id IS NOT NULL resolve o problema do UNKNOWN, mas o NOT EXISTS continua mais seguro porque não depende de ninguém lembrar disso quando a coluna passar a aceitar NULL. Outra alternativa equivalente é o LEFT JOIN com WHERE i.external_id IS NULL, só que aí vale checar duplicidade em identity_mappings, pois ela multiplica as linhas do lado esquerdo. No Postgres, o NOT IN também costuma perder desempenho em tabelas grandes porque o plano não consegue virar um anti-join, enquanto o NOT EXISTS vira. Vale comparar os dois com EXPLAIN ANALYZE antes de rodar o cleanup.
Tem outro ponto que eu observaria em um job de limpeza em produção: a concorrência entre transações.
Mesmo usando
NOT EXISTScorretamente, ainda existe uma possível condição de corrida. Imagine que uma transação verifica a ausência de um registro emidentity_mappings, enquanto outra está criando esse mesmo vínculo para o(tenant_id, external_id).Por causa do MVCC do PostgreSQL, a consulta de limpeza trabalha com seu próprio snapshot. Se o novo vínculo ainda não estiver visível nesse snapshot, o registro poderá continuar elegível para exclusão, mesmo que a outra transação confirme a criação do vínculo durante esse processo.
Isso é diferente do problema com
NULL, e trocarNOT INporNOT EXISTSnão resolve essa situação.Eu testaria esse cenário com duas sessões concorrentes, principalmente quando a criação de vínculos e o cleanup podem acontecer ao mesmo tempo. Se essa relação precisa permanecer consistente, os dois fluxos precisam de alguma estratégia de coordenação, como serializar operações pela mesma chave de identidade ou usar transações
SERIALIZABLEcom tratamento adequado de retries.Uma consulta pode estar logicamente correta para o snapshot que recebeu e, ainda assim, produzir um resultado inesperado quando existem gravações concorrentes.