Being a DBA, this is a recurrent issue I have every several months with developers: Why my "not in" query is not returning any rows?

One query, two tables:

select count(1) 
from first_table 
where my_row NOT IN (select my_row from second_table);

There's two downsides in using an inner query (subquery) with a NOT IN. The first one is performance: "not in" does not scale well when the second_table is big. You are forcing Oracle to do a full scan on the table, and then compare my_row with the list of values. This is not good.

The second issue is NULL. If my_row is Nullable, and in fact there are null values in some rows of the second_table, then the NOT IN will always give you "TRUE". This is because a NULL value compared with a NOT NULL value always returns FALSE, and since you are using NOT, then FALSE becames TRUE and even if there is no match you will return the row at the end. A quick workaround is to add "where my_row is not null" in the subquery, but the first issue will still be there.

Both issues are solved using NOT EXISTS:

select count(1) 
from first_table t1 
where NOT EXISTS (select 1 
                  from second_table t2 
                  where t2.my_row = t1.my_row);

If you have an index on my_row this will scale perfectly no matter how big is your table.