1

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.

4
  • Are there NULL values in ID?
    – user1822
    May 12, 2020 at 20:23
  • Does it matter which table the NULLS are in or would either table result in these values?
    – Ben C Wang
    May 12, 2020 at 20:50
  • NULL values in the table from the sub-select
    – user1822
    May 12, 2020 at 20:51
  • Read the manual re the functionality you are using.
    – philipxy
    May 13, 2020 at 0:03

1 Answer 1

3

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.

1
  • 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);"
    – Ben C Wang
    Sep 27, 2021 at 16:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.