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?