The COUNT () function combined with the NOT IN clause does not work properly with the varchar field (T-SQL)

Question

I came across a strange situation, trying to count the number of rows that DO NOT have varchar values ​​specified by the select statement. Well, that sounds confusing even to me, so let me give you an example:

Say I have a MyField field in SomeTable, and I want to calculate how many rows MyField values ​​do not belong to the domain defined by MyOtherField values ​​in SomeOtherTable. In other words, suppose I have MyOtherField = {1, 2, 3}, I want to calculate how many rows the value of MyField is not 1, 2 or 3. For this, I would use the following query:

SELECT COUNT(*) FROM SomeTable WHERE ([MyField] NOT IN (SELECT MyOtherField FROM SomeOtherTable)) 

And it works like a charm. Please note, however, that MyField and MyOtherField are introduced. If I try to run the same request, with the exception of fields with varchar typed, its return value is 0, although I know that there are incorrect values, I put them there! And if, however, I try to count the opposite (how many ARE lines in the domain, the opposite of what I want, so many how many lines) by simply suppressing the “NOT” clause in the query above ... Well, THAT works! ¬¬

Yes, there should be many workarounds, but I would like to know why this does not work as it should. Also, I can't just change the whole request, since most of it is embedded in C # code and basically the only part I have the freedom to change that will not affect any other part of the software is the select statement that matches the domain (everything that is included in the NOT IN clause). I hope I realized that someone can help me.

Thanks in advance.

Answer 1

For NOT IN, this is always wrong if the subquery returns NULL. The accepted answer to this question elegantly describes why.

The NULLability value of the column value is independent of the data type used: most likely your varchar columns are NULL

Cope with this, use NOT EXISTS. For non-empty values, it works the same as NOT IN, therefore it is compatible

 SELECT COUNT(*) FROM SomeTable S1 WHERE NOT EXISTS (SELECT * FROm SomeOtherTable S2 WHERE S1.[MyField] = S2.MyOtherField) 

Answer 2

gbn has a more complete answer, but it doesn't bother me to remember all this. Instead, I have a religious habit of filtering zeros from my IN clauses:

 SELECT COUNT(*) FROM SomeTable WHERE [MyField] NOT IN ( SELECT MyOtherField FROM SomeOtherTable WHERE MyOtherField is not null )