---
title: "Discussion on \"SQL NOT IN vs NOT EXISTS: Handling NULL Values Safely\" | Hashnode"
description: "Discussion on \"SQL NOT IN vs NOT EXISTS: Handling NULL Values Safely\". Learn why SQL NOT IN returns zero rows when subqueries contain NULLs, how three-valued logic causes silent failures, and why NOT EXISTS is safer."
url: "https://hashnode.com/posts/sql-not-in-null-trap/6ae08e9544efbc8a069a8dd1"
---

[Feed](https://hashnode.com/feed) /

Discussion

# [SQL NOT IN vs NOT EXISTS: Handling NULL Values Safely](https://doogal.dev/sql-not-in-null-trap?utm_source=hashnode&utm_medium=feed)

Learn why SQL NOT IN returns zero rows when subqueries contain NULLs, how three-valued logic causes silent failures, and why NOT EXISTS is safer.

Oct 27 · 5 min read · doogal.dev  
Read the article

-   [#sql](https://hashnode.com/tag/sql)
-   [#databases](https://hashnode.com/tag/databases)
-   [#backend](https://hashnode.com/tag/backend)
-   [#softwareengineering](https://hashnode.com/tag/softwareengineering)

## Written by

[Doogal Simpson](https://hashnode.com/@doogal)

@doogal

  
[View profile](https://hashnode.com/@doogal)

"hey doogal, what is .... ?"

## Responses 5

Markdown supported · ⌘↵ to post

[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.'

[YouBeyond](https://hashnode.com/@youbeyondai)

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

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.

[Viktor Novak](https://hashnode.com/@viktornovak)

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

The planner treats NOT IN as a different animal than NOT EXISTS, and that difference is rarely documented.

[Tobias Lang](https://hashnode.com/@tobiaslang)

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

The NOT IN trap still catches experienced people during migrations.
