Skip to content
  • Home
  • About
  • No Access
  • Privacy Policy
Bits of .NET
Bits of .NET
  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy

Bits of .NET

Daily micro-tips for C#, SQL, performance, and scalable backend engineering.

  • Asp.Net Core
  • C#
  • SQL
  • JavaScript
  • CSS
  • About
  • ErcanOPAK.com
  • No Access
  • Privacy Policy
SQL

SQL Server: Why NULL Values Break a Simple NOT IN Subquery

- 06.09.26 - ErcanOPAK

🗄️ Your NOT IN Subquery Returns Nothing at All – Because of One NULL Value

A `NOT IN` subquery that should exclude a handful of specific IDs instead returns ZERO rows entirely, not just the wrong rows – and the cause is almost always a single NULL value hiding in the subquery’s result set, because SQL’s three-valued logic makes `NOT IN` against a list containing NULL evaluate to UNKNOWN for every single row, which the WHERE clause treats the same as false.

🔎 The Problem

SELECT * FROM Customers
WHERE CustomerId NOT IN (
    SELECT CustomerId FROM BlacklistedCustomers
);

-- If BlacklistedCustomers.CustomerId contains even ONE NULL row (an
-- incomplete record, a bad data entry), the entire query returns ZERO
-- rows - not "all customers except the blacklisted ones", but nothing
-- at all. This is because `x NOT IN (1, 2, NULL)` evaluates to
-- `x <> 1 AND x <> 2 AND x <> NULL` - and `x <> NULL` is UNKNOWN for
-- every value of x, which poisons the entire AND chain to UNKNOWN,
-- and UNKNOWN is treated as "don'"'"'t include this row" by WHERE.

✅ Fix: Filter Out NULLs, or Use NOT EXISTS Instead

  • The quick fix: explicitly exclude NULLs from the subquery – `WHERE CustomerId NOT IN (SELECT CustomerId FROM BlacklistedCustomers WHERE CustomerId IS NOT NULL)` – which resolves the immediate symptom with a one-line change.
  • The more robust fix, recommended as a general habit: use `NOT EXISTS` instead of `NOT IN` for this kind of subquery – `WHERE NOT EXISTS (SELECT 1 FROM BlacklistedCustomers b WHERE b.CustomerId = Customers.CustomerId)` – which is immune to this NULL trap entirely, since it never evaluates an equality/inequality against NULL the same way.

⚠️ Why This Is a Trap Worth Remembering Permanently

  • The subquery returning a NULL is often completely unrelated to the actual business logic – a bad data entry, an unrelated bug elsewhere that let a NULL slip into a column that should never have one – which makes the resulting empty result set from the OUTER query feel disconnected from its actual root cause.
  • This exact trap is common enough that many style guides recommend NEVER using `NOT IN` with a subquery, specifically because there’s no way to guarantee the subquery’s result set stays NULL-free forever as the schema or data evolves – `NOT EXISTS` doesn’t have this failure mode at all.

NOT IN doesn’t fail loudly when a NULL sneaks into its list – it fails by going quiet, returning nothing, and leaving no error to point at the real cause.

— Database Engineer

Related posts:

SQL Server JSON Support: NoSQL Flexibility with SQL Power

SQL Server JSON Support: NoSQL Flexibility with SQL Power

SQL: Optimize Complex Joins for Large Datasets

SQL: Optimize Complex Joins for Large Datasets

SQL: Use CTEs to Write Readable Complex Queries

SQL: Use CTEs to Write Readable Complex Queries

Views: 12

Post navigation

Docker: Fix a Compose File Where One Service Starts Before Its Dependency Is Ready
SQL Server: Fix a Column Default Value That Doesn’t Apply to Rows Inserted by the App

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

November 2026
M T W T F S S
 1
2345678
9101112131415
16171819202122
23242526272829
30  
« Oct    

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)
  • How to enable, disable and check if Service Broker is enabled on a database in SQL Server (653)
  • Add Constraint to SQL Table to ensure email contains @ (609)
  • Average of all values in a column that are not zero in SQL (570)
  • How to use Map Mode for Vertical Scroll Mode in Visual Studio (547)
  • Find numbers with more than two decimal places in SQL (475)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit
  • SQL Server: Fix ROW_NUMBER Ties Reordering Between Identical Runs
  • C#: Fix an Enum HasFlag Returning True for the Zero Value
  • WordPress: Fix Featured Images Vanishing After a CDN Migration
  • Photoshop: Fix Content-Aware Fill Smearing a Repeating Pattern
  • Asp.Net Core: Fix Antiforgery Tokens Rejected Behind a Reverse Proxy

Most Viewed Posts

  • How to make theater mode the default for Youtube (1,055)
  • Get the User Name and Domain Name from an Email Address in SQL (980)
  • How to add default value for Entity Framework migrations for DateTime and Bool (977)
  • Get the First and Last Word from a String or Sentence in SQL (858)
  • How to select distinct rows in a datatable in C# (854)

Recent Posts

  • C#: Fix a ConditionalWeakTable Entry Vanishing Too Soon
  • Docker: Fix a Stale COPY Layer After Editing .dockerignore
  • Photoshop: Fix a Pattern Overlay Scaling Wrong on a High-DPI File
  • SQL Server: Fix a Computed Column Index Skipped by Implicit Cast
  • HTML5: Fix a required Input Inside a Hidden Tab Blocking Submit

Social

  • ErcanOPAK.com
  • GoodReads
  • LetterBoxD
  • Linkedin
  • The Blog
  • Twitter
© 2026 Bits of .NET | Built with Xblog Plus free WordPress theme by wpthemespace.com