Why would a Select from Tabke1 where FIeldA not in (Select FieldA from Table2) fail if I manually co-CodePudding

I ran

Select Term from TableA where Term not in (Select Name from TableB)

successfully. Yet when I tried another table

Select Term from TableA where Term not in (Select Name from TableC)

It returns a NULL set. I manuallyt confirmed there are literaly 1000s of records in TableA not in TableB and that the fields are correct.

Is there some other consideration I am not taking into account/understanding about what seems to be a simply query?

Update: Interestingly enough this alternative query worked:

 Select Term from TableA where not exist (Select Name from TableB where TableA.Term=TableC.Name)

So while I solved the problem at hand I am still curious why the first query worked for both tables but only the second worked on TableC

CodePudding user response：

For a simple example, consider the following query:

SELECT *
FROM Table
WHERE ID NOT IN (1, 2, NULL);

This is semantically equivalent to:

SELECT *
FROM Table
WHERE ID <> 1 AND ID <> 2 AND ID <> NULL;

The issue is with the last predicate (ID <> NULL). Nothing can be equal to null (not even null) and nothing can be not equal to null. The predicate returns neither true nor false, it returns NULL.

e.g.

ID	ID <> 1	ID <> 2	ID <> NULL
1	FALSE	TRUE	NULL
2	TRUE	FALSE	NULL
3	TRUE	TRUE	NULL

Since you need 3 "TRUE"s to satisfy the AND condition, no rows are returned, since none resolve to true for all 3 predicates.

You can get your NOT IN to work by excluding nulls, e.g.

SELECT Term 
FROM TableA 
WHERE Term NOT IN (SELECT Name FROM TableC WHERE Name IS NOT NULL);

But for this reason, and the potential for name clashes I always use NOT EXISTS.