COUNT() Function in conjunction with NOT IN clause not working properly with varchar field (T-SQL)

Question

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

Let's say I have a field "MyField" in "SomeTable" and I want to count in how many rows MyField values do not belong to a domain defined by the values of "MyOtherField" in "SomeOtherTable". In other words, suppose that I have MyOtherField = {1, 2, 3}, I wanna count in how many rows MyField value are not 1, 2 or 3. For that, I'd use the following query:

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

And it works like a charm. Notice however that MyField and MyOtherField are int typed. If I try to run the exact same query, except for varchar typed fields, its returning value is 0 even though I know that there are wrong values, I put them there! And if I, however, try to count the opposite (how many rows ARE in the domain opposed to what I want that is how many rows are not) simply by supressing the "NOT" clause in the query above... Well, THAT works! ¬¬

Yeah, there must be tons of workarounds to this but I'd like to know why it doesn't work the way it should. Furthermore, I can't simply alter the whole query as most of it is built inside a C# code and basically the only part I have freedom to change that won't have an impact in any other part of the software is the select statement that corresponds to the domain (whatever comes in the NOT IN clause). I hope I made myself clear and someone out there could help me out.

Thanks in advance.

Answer 1

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

The NULLability of a column value is independent of the datatype used too: most likely your varchar columns has NULL values

Do deal with this, use NOT EXISTS. For non-null values, it works the same as NOT IN so 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 I can't be bothered to remember all that. Instead I have the religious habit of filtering nulls out of my IN clauses:

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