Difference IN vs EXISTS

This is a repeat question in almost every PL/SQL Interview. 

Well, both work very differently. 

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 processed first and then joined to the original table -- typically.

As opposed to 

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

Outer query is processed 1st, for every row returned by t1, the inner query runs once and is evaluated with the outer row.

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 whereas the first query can make use of an index on T1 (x).

So, when is EXISTS appropriate and IN appropriate?

Lets say the result of the subquery
( select y from T2 )

is "huge" and takes a long time. But the outer table T1 is relatively small and executing the subquery is faster due to nice indexes joining T1 and T2. (x and y in the example above) Then exists will be faster as the time to do a  full scan on T1 and do the index probe into T2 could be less then the time to simply full scan T2 to build the subquery.

Lets say the result of the subquery is small -- then IN is typicaly more appropriate.

If both the subquery and the outer table are huge -- either might work as well as the other -- depends on the indexes and other factors. [AskTom]