Oracle SQL NOT IN and NOT EXISTS returning different values. Why?

Question

I have two tables both containing an ID column which is NUMBER(38).

Table A has ~200k rows which is ALL IDs in existence, while table B has ~15k rows. Table B is a subset of Table A, which means all IDs in B exist in A.

(This is just an example, table names and column names are different and not matching, but I'm trying to make this easier to explain)

I am trying to find all of the records in table A whose ID does not exist in table B:

select ID from tableA where ID not in (select ID from tableB);

Returns 0 rows. This doesn't make sense to me, as I am expecting to get back 85k rows.

When I reverse the statement, I get 15k rows returned which makes sense because B is a subset of A.

Alternatively, I tried using NOT EXISTS:

select ID from TableA A where NOT EXISTS (select ID from tableB B where B.ID = A.ID);

And this result returns 85k records VS 0 from the example above.

I've never seen this behavior before, does anyone know what might be going on and why the NOT IN isn't returning the same results as NOT EXISTS? In this situation, NOT EXISTS is working as expected, but NOT IN is showing 0 records which is wrong.

• user1822: Are there NULL values in ID?
• Ben C Wang: Does it matter which table the NULLS are in or would either table result in these values?
• user1822: NULL values in the table from the sub-select
• philipxy: Read the manual re the functionality you are using.

Answer 1

That is normal behaviour. NOT IN and NOT EXISTS is not the same. They return same results only as long as nulls are not involved.

SQL> create table tablea (id number);
Table created.
SQL> create table tableb (id number);
Table created.
SQL> insert into tablea values(1);
1 row created.
SQL> insert into tablea values(2);
1 row created.
SQL> insert into tableb values(2);
1 row created.
SQL> insert into tableb values(3);
1 row created.
SQL> commit;
Commit complete.
SQL> select ID from tableA where ID not in (select ID from tableB);
 ID
----------
 1
SQL> select ID from TableA A where NOT EXISTS (select ID from tableB B where B.ID = A.ID);
 ID
----------
 1
SQL> insert into tableb values (null);
1 row created.
SQL> commit;
Commit complete.
SQL> select ID from tableA where ID not in (select ID from tableB);
no rows selected
SQL> select ID from TableA A where NOT EXISTS (select ID from tableB B where B.ID = A.ID);
 ID
----------
 1

Your subquery returns nulls.

• Ben C Wang: Does adding 'not null' to the 'not in' statement, then make it the same as the not exists? i.e. "select ID from tableA where ID not in (select ID from tableB where ID is not null);"