IN (vs) EXISTS

At which situation, IN is better then EXISTS?

IN & EXISTS are processed in different way. But we can use IN in the place of EXISTS, also EXISTS can be used in the place of IN.



select * from emp where deptno in(select deptno from dept);

is typically processed as :

select * from emp, ( select distinct deptno from dept ) dept where emp.deptno = dept.deptno;

The subquery is evaluated, distinct'ed, indexed (or hashed or sorted) and then joined to the original table.

As opposed to

select * from emp where exists(select null from dept where deptno = emp.deptno)

That is processed more like:

for x in ( select * from emp ) loop

if ( exists ( select null from dept where deptno = emp.deptno )

then OUTPUT THE RECORD

end if

end loop

It always results in a full scan of EMP in EXISTS clause, whereas the first query can make use of an index on EMP.DEPTNO.

So, when is EXISTS appropriate and IN appropriate?

Lets say the result of the subquery is "huge" and takes a long time, outer query is small. Then IN is not good option. Because, it has to distinct the data. EXISTS can be quite efficient for this circumstances.

If the result of sub query is small, and then IN is good option. EXISTS is not good choice.

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.

When we tune the query, we can try with both IN & EXISTS and we can decide which one would be better...

If you need more info about this, please read the asktom article..

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:953229842074