NOT IN vs. NOT EXISTS when NULL values are involved

NOT IN does not behave as you would expect when NULL values are involved. Suppose you have the following tables:

Now try the following query:

   
SELECT *
FROM table_a a
WHERE a.cd_field NOT IN (SELECT b.cd_field FROM table_b b);

Does the previous query return any row? The answer is NO it doesn’t, even if there is a value (2) in table_a.cd_field which is not present in table_b.cd_field.

I think this is because NOT IN internally uses the inequality operator. You should never use the equality or inequality operators with NULL, but IS NULL and IS NOT NULL, respectively. For this reason, in SQL, NULL is not equal to NULL. If you don’t believe me then try the following query:

    
SELECT 'test' c
FROM dual
WHERE NULL = NULL;

It returns 0 rows.

So how can you work this out? Enter the EXISTS (and NOT EXISTS) operator.

Try the following query:

     
SELECT *
FROM table_a a
WHERE NOT EXISTS (SELECT 1 FROM table_b b WHERE a.cd_field = b.cd_field);

The previous query returns 1 row, that is what you would expect since there is a row in table_a with cd_field = 2 which is not present in table_b.cd_field.

From now on beware of using NOT IN and IN when NULL values are involved.