NOT IN / NOT EXISTS

 It is recommend to use NOT EXIST instead of NOT IN ; do you know/thought  why ?  ! .




You dont see any differences here because the tables are small with less data and also there will not be any difference in the explain plan too.For very large tables performance improvement are seen by using NOT EXISTS instead  of  NOT IN ; so test it and see whats the difference .


  EXAMPLE

   create table oracle4u ( orclid number(10), constraint pk primary key(orclid) ,s_name varchar2(100) );


   create table "oracle4u.com" ( orcl4id number(10), constraint opp primary key(orcl4id) ,s_name varchar2(100) );


   insert into oracle4u select rownum,OBJECT_NAME  from SYS.ALL_OBJECTS;


   insert into oracle4ucom select rownum,TABLE_NAME  from SYS.ALL_tables union select null,'TEST'  from dual

commit;
-------------------


select * from oracle4u pp where pp.orclid not in (select fg.orcl4id  from oracle4ucom fg)


select * from oracle4u pp where pp.orclid not in (select fg.orcl4id  from   oracle4ucom fg where fg.orcl4id is not null)


select * from oracle4u pp where  not exists  (select fg.orcl4id  from oracle4ucom fg where pp.orclid=fg.orcl4id )


select *  from oracle4u pp  Left Outer JOIN oracle4ucom fg ON pp.orclid=fg.orcl4id  WHERE fg.orcl4id IS NULL