We should use Exists or In based on the no of records inner and outer query is expected to return.Using Exists : Here the outer query is executed first and then inner query is excecuted for each value of outer query. So use this when outer query contains less records.Using IN : Here the inner query is executed first and then outer query is excecuted for each value of inner query. So use this when inner query contains less records.
Above answer was rated as good by the following members: ratna82
RE: Compare EXISTS and IN Usage with advantages and di...
Use Exists whenever possible. EXISTS only checks the existence of records (True/False), and in the case of IN each and every records will be checked.  performace wise EXISTS is better.
RE: Compare EXISTS and IN Usage with advantages and disadvantages.
IN is used in normal SELECT queries:
e.g. SELECT * FROM EMP WHERE EMPID IN (12,13,15)
We cannot use EXIST to fetch the rows with 12,13,15 EMP IDs
EXIST only tells if a SELECT query will fetch any row - true, no rows - false
e.g. IF EXISTS(SELECT * FROM EMP WHERE EMPID IN (12,13,15)) DELETE FROM EMP WHERE EMPID IN (12,13,15) this will fire the DELETE statement only once as next time there will be no rows with these EMPIDs
RE: Compare EXISTS and IN Usage with advantages and disadvantages.
Firstly, in common with Tom Kyte, I could scream hearing comments that IN is "better" or faster than exists. Nonsense.
There are situations where IN is much faster (and vice versa).
eg. select * from MASSIVE_TABLE T where T.indexed_field in (select result from VERY_SMALL_RESULT_SET)
Using IN, if resulting in the index on MASSIVE_TABLE being used be MUCH faster than an exists.
For a full comparison see Tom Kytes explaination at:- http://asktom.oracle.com/pls/asktom/f?p=100:11:2957834641539512::::P11_QUESTION_ID:953229842074
From memory, here are some differences:-
1. IN a subquery - must retrieve the entire query set before evaluating the outer query
2. EXISTS on the other hand will stop executing a sub-query when the first entry is found. This may in some cases favour EXISTS over IN
3. "Where EXISTS (select null from..." clearly cannot use any index on outer query table whereas IN can. (See explaination above)
In short, there are cases where IN is better than EXISTS (it can mean an index on the outer query may be used). It depends upon the relative size of the "inner" and "outer" tables, and indexes in place.
RE: Compare EXISTS and IN Usage with advantages and disadvantages.
IN or EXISTS which gives better performance depends upon the situation.
While using IN , optimizer runs subquery first and then joins it with main dataset. Table in the subqery is small and table in the main query is large then IN usaully makes sense.
Using EXISTS, optimizer runs main query first and then applies dataset to the subquery. If the main table size is small and subquery table size is large then EXISTS usually makes sense.
RE: Compare EXISTS and IN Usage with advantages and disadvantages.
IN or EXISTS which gives better performance depends upon the situation.
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;
The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to the original table -- typically.
While using IN , optimizer runs subquery first and then joins it with main dataset. Table in the subqery is small and table in the main query is large then IN usaully makes sense.
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 results in a full scan of T1
Using EXISTS, optimizer runs main query first and then applies dataset to the subquery. If the main table size is small and subquery table size is large then EXISTS usually makes sense.
RE: Compare EXISTS and IN Usage with advantages and disadvantages.
IN or EXISTS which gives better performance depends upon the situation.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;The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to the original table -- typically.While using IN , optimizer runs subquery first and then joins it with main dataset. Table in the subqery is small and table in the main query is large then IN usaully makes sense.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 loopIt always results in a full scan of T1 Using EXISTS, optimizer runs main query first and then applies dataset to the subquery. If the main table size is small and subquery table size is large then EXISTS usually makes sense.
RE: Compare EXISTS and IN Usage with advantages and disadvantages.
We should use Exists or In based on the no of records inner and outer query is expected to return.Using Exists : Here the outer query is executed first and then inner query is excecuted for each value of outer query. So use this when outer query contains less records.Using IN : Here the inner query is executed first and then outer query is excecuted for each value of inner query. So use this when inner query contains less records.
1 
0