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

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

NOT IN

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

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;

See also