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

oracle

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.

Best Answer

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.

Related Question