Why is SQL not matching two strings?

Question

As I said in the title, I have a query that doesn't return what's expected and it isn't logical... Here is the case :

Table A -> id INT, value VARCHAR

Table B -> id INT, value VARCHAR

When I run that query SELECT value FROM A WHERE value NOT IN (SELECT value FROM B);

I expect that query to return all values that are not in the table B, but it returns all the values.

Let's assume that A contains

1. '425415'
2. '698741'

With that query : SELECT value FROM A WHERE value NOT IN ('698741','425415'); it still returns all the values without triggering the NOT IN...

Answer 1

This is because you have a NULL in the "B" table, try this:

SELECT A.[value]
FROM A
LEFT JOIN B ON A.[value] = B.[value]
WHERE B.[value] IS NULL;

Or if you want to keep your code just add IS NOT NULL:

SELECT [value] 
FROM A 
WHERE [value] NOT IN (SELECT [value] FROM B WHERE [value] IS NOT NULL);