T-SQL query: where column not in is giving wrong result

Question

I have two table A and table B, the common column in both table is Name, I want to know what are name in Table A that is not in table B

when I do:

Select Name from A where Name not in (Select Name from B)

I am sure there are 2 name in Table A that is not in table B

but the result returns nothing

These name column in table A and B has the same datatype varchar(50)

so I copy the result of Name column and Insert into a new table and do the same query, and this time it returns the right result. what bug could this be?

example:

Table A
Name:
Kevin
Dexter
David 
John
Marry

Table B
Name:
Kevin
Dexter
David 

So the query should return 'John', 'Marry' but it doesn't return in my original table, but it returns in another table I create and insert.

Thanks!

Answer 1

You probably have a NULL name on B, this makes the NOT IN false for every row. You should use NOT EXISTS instead:

SELECT Name 
FROM A 
WHERE NOT EXISTS (SELECT 1 FROM B 
                  WHERE A.Name = B.Name)

Answer 2

Obviously it is because of NULL value on some rows in table B. You can do what you want with EXCEPT

SELECT Name FROM TableA
EXCEPT
SELECT Name FROM TableB