IN Vs EXIST and NOT IN VS NOT EXIST in oracle

1. IN condition Vs EXIST   condition
==========================

1.Use IN with list of sub-query (Where servey_date  in ('20015','2016'))
2. EXIST  looking for at least one row to return return true
3.If inner query has less records then;then use the IN
4. If inner query has more records then; the outer query then use EXIST.
 (Thumb rule to use the IN and EXIST)

IN query processed as
===============

Select * from T1 where x in ( select y from T2 )
is typically processed as:

select *
  from t1, ( select distinct y from t2 ) t2
 where t1.x = t2.y;

Exist Processed as folowing
===================

select * from t1 where exists ( select null from t2 where y = x )

That is processed more like:


   for x in ( select * from t1 )
   loop
      if ( exists ( select null from t2 where y = x.x )
      then 
         OUTPUT THE RECORD
      end if
   end loop

 - It always result into FULL_TABLE scan on the T1.

  -If your goal is the FIRST row exists might totally blow away IN this is 

the exception to thumb rule.

 Link to source page:  IN_VS_EXIST_ASK_TOM

NOT IN can be just as efficient as NOT EXISTS -- many orders of magnitude BETTER 

even -- "anti-join" can be used (if the subquery is known to not return nulls) 

Anti-Join fails when NULL;Therefore, a NOT IN operation would fail

 if the result set being probed returns a NULL. In such a case, 

the results of a NOT IN query is 0 rows  while a NOT EXISTS query

 would still show the rows present in the one table but not in the other table.