# 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:

```sql
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:

```text
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`:

```sql
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:

```sql
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?