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

I've seen SQL bugs that are easy to miss because nothing actually crashes. The query runs, the database returns a result, and the code moves on. You only notice something is wrong when a cleanup job skips records it should have processed.

NOT IN can cause exactly this kind of problem when NULL gets involved.

The problem isn't where you'd expect

Imagine you're cleaning up stale identity mappings in a multi-tenant platform.

You have a table of active memberships and another table that maps external identities to internal accounts. You want to find memberships that don't have a matching 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
);

Looks reasonable. Find the external IDs that aren't in the mapping table.

Now imagine identity_mappings.external_id contains these values:

ext_1042
ext_2087
NULL
ext_3911

That NULL changes the result.

SQL doesn't treat NULL as an ordinary value. It represents an unknown value, so comparisons involving it can evaluate to UNKNOWN rather than TRUE or FALSE.

For a value that doesn't match any known ID, SQL can't establish that it differs from every value in the subquery because one of those values is unknown. The NOT IN condition therefore evaluates to UNKNOWN, and the WHERE clause filters the row out.

The query doesn't throw an error. It just returns fewer rows than you expected.

That's the part that makes this bug annoying to track down.

Why changing the query isn't the whole fix

A common alternative is 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
);

This avoids the specific problem caused by an unrelated NULL in the subquery. The database checks whether a matching row exists instead of comparing the outer value against every result in a list.

But there's another issue hiding in the original query.

This is a multi-tenant system. The same external ID might legitimately exist in more than one tenant. If the relationship is defined by both tenant_id and external_id, comparing only the external ID is incomplete.

The query should reflect that:

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 system actually cares about.

There's still a decision to make about nullable external IDs. With the equality comparison above, a NULL external ID won't match another NULL external ID. If the business rules say those records should be treated as matching, the query needs different logic. If NULL means the identity hasn't been linked yet, treating it as unmatched might be exactly what you want.

That decision belongs in the data model and business rules, not in an assumption about how SQL will behave.

The tests I'd add before running this in production

For a cleanup job like this, I'd test more than a normal matching record and an orphaned record.

• A mapping exists for the same external ID and the same tenant.
• The same external ID exists under a different tenant.
• The mapping table contains a NULL external ID.
• The membership itself has a NULL external ID.
• Multiple mappings exist for the same tenant and external ID.

I'd also check whether the database schema enforces the intended relationship. If (tenant_id, external_id) is supposed to identify a mapping uniquely, a suitable unique constraint may prevent inconsistent data from accumulating in the first place. Whether external_id should be nullable is a separate schema decision.

And before running a destructive cleanup, I'd inspect the candidate rows and verify the expected count. A query returning valid SQL results doesn't mean it's safe to delete everything it returns.

The bigger lesson

NOT IN versus NOT EXISTS is worth understanding, but it's only one part of the problem.

A query can be syntactically valid and still describe the wrong relationship. It can handle NULL correctly and still cross tenant boundaries. It can pass tests built around clean data and fail as soon as production data gets messy.

When I review an anti-join, I want to know three things: what counts as a match, what NULL is supposed to mean, and which columns actually define the relationship.

The SQL comes after that.

Have you run into a query that looked correct in code review but turned out to be checking the wrong relationship?