The SQL NOT IN Trap: When NULL Quietly Breaks Your Query

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 NULL external ID.
• The membership itself has a NULL external 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:

1. What exactly makes two records a match?
2. What should happen when one of the relevant values is NULL?
3. 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?