which situation EXIST condition is better than IN, and vice versa.

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).