Search notes:

Oracle Subquery: IN, NOT IN

Test table and data

create table tq84_a (id number,  txt varchar2(10));
create table tq84_b (id number,  txt varchar2(10));

begin
   insert into tq84_a values (   1, 'one'  );
   insert into tq84_a values (   2, 'two'  );
   insert into tq84_a values (   3, 'three');
   insert into tq84_a values (null, 'null' );

   insert into tq84_b values (   2, 'two'  );
   insert into tq84_b values (   3, 'three');
   insert into tq84_b values (   4, 'four' );
   insert into tq84_b values (null, 'null' );
end;
/

IN

The record whose id is null is not returned:

select * from tq84_a
where
   id in (select id from tq84_b);
--
--         ID TXT       
-- ---------- ----------
--          2 two       
--          3 three

NOT IN

When the subquery returns nulls to the NOT IN operator, no record is returned:

select * from tq84_a
where
   id not in (select id from tq84_b);
--
-- no rows selected

The following query's subquery does not return null values:

select * from tq84_a
where
   id not in (select id from tq84_b where id is not null);
--
        ID TXT       
---------- ----------
         1 one

Cleaning up

drop   table tq84_a;
drop   table tq84_b;

Oracle Subquery: IN, NOT IN

Test table and data

IN

NOT IN

Cleaning up

See also