---
title: "Comment by Sina Rezaei on \"SQL NOT IN vs NOT EXISTS: Handling NULL Values Safely\" | Hashnode"
description: "The migration angle gets even trickier with multi-tenant data. Imagine cleaning up orphaned membership records using an anti-join against an external identity m"
url: "https://hashnode.com/posts/sql-not-in-null-trap/6ae08e9544efbc8a069a8dd1/comment/6ae3312e62ab48dd145dde8d"
---

[Discussion](https://hashnode.com/posts/sql-not-in-null-trap/6ae08e9544efbc8a069a8dd1)

Comment [SQL NOT IN vs NOT EXISTS: Handling NULL Values Safely](https://hashnode.com/posts/sql-not-in-null-trap/6ae08e9544efbc8a069a8dd1)

[Sina Rezaei](https://hashnode.com/@sinarezaei)

[Oct 29](https://hashnode.com/posts/sql-not-in-null-trap/6ae08e9544efbc8a069a8dd1/comment/6ae3312e62ab48dd145dde8d)

The migration angle gets even trickier with multi-tenant data. Imagine cleaning up orphaned membership records using an anti-join against an external identity mapping table. If the query only checks the user ID and ignores the tenant ID, you can accidentally classify a valid membership as orphaned because the same user exists under a different tenant.

I'd test the NULL cases separately, but also make the tenant boundary part of the correlation itself. Something like `NOT EXISTS (... WHERE mapping.tenant_id = membership.tenant_id AND mapping.external_id = membership.external_id)` makes the intended relationship explicit. Whether a NULL external ID should count as unmatched is a separate business rule, not something I'd leave to SQL's three-valued logic.

I've seen people focus on swapping `NOT IN` for `NOT EXISTS` and miss the more serious bug: the query is technically valid but defines the wrong relationship. Do you usually test these anti-joins against cross-tenant collisions as well as NULLs?

[SMTasker](https://hashnode.com/@smtasker)

[Nov 3](https://hashnode.com/posts/sql-not-in-null-trap/6ae08e9544efbc8a069a8dd1/comment/6ae3312e62ab48dd145dde8d#reply-6ae9d180f84330731b8d708f)

Your migration example also raises a timing question: what happens if a mapping is created after the preview but before cleanup? I'd keep the tenant-aware predicate in the mutation instead of deleting only by a saved candidate ID list, then test the concurrent-insert policy separately. That makes 'unmatched when previewed' distinct from 'safe to remove now.'
