which situation EXIST condition is better than IN, and vice versa.
ANSWER:
Result of the subquery is small Then "IN" is typicaly more appropriate.
and
Result of the subquery is big/large/long Then "EXIST" is more appropriate.
suppose :-
select * from table_1
where id in (select Id from table_2)
Is normaly processed as:
select * from table_1 , ( select distinct y from table_2 ) t2
where table_1.x = table_2.y;
Means the subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to the original table
and oposite is (EXIST)
select * from table_1 where exists ( select null from table_2 where table_2.y = table_1.x )
That is processed more like:
for x in ( select * from table_1 ) loop
if ( exists ( select null from table_2 where table_2.y = x.x ) then
OUTPUT THE RECORD end
end if
end loop
It always results in a full scan of Table_1 whereas the first query can make use of an index on Table_1(x).
