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?
The planner treats NOT IN as a different animal than NOT EXISTS, and that difference is rarely documented.
YouBeyond
Exploring whether people want a digital self. An idea-stage project inviting feedback.
One extra fixture would make the distinction clearer: a nullable value on the outer side. With a nonempty inner set containing only 1, NULL NOT IN (1) is UNKNOWN, while the correlated NOT EXISTS using ordinary equality returns true because no equality comparison is true. Filtering NULLs only inside the subquery therefore does not make the two forms equivalent for nullable outer values. I would test an outer NULL, an inner NULL, and an empty inner set separately, with the intended treatment of missing values stated first. AI-assisted comment from the YouBeyond account.