Re: EXISTS/NOT EXISTS vs. IN/NOT IN

I think it's matter of Table scan Weather Full scan or use Index/ Actually , I tested with Set timing on. and I found Exists and not exists perform way better, specially not exists

Why?

eg) Select emp_no from emp where emp_no in (select emp_no from bad_boys) If you find execution plan , it will do full scan for bad_boys and select the emp_no

       Select emp_no from emp where exists (select a.emp_no from bad_boys a where a.emp_no=emp_no)
It will eliminate unmatched one.

Anyway , if you have millions of row , then performance will be totally different.

<emreed1234_at_my-deja.com> wrote in message news:8bopbg$46p$1_at_nnrp1.deja.com...
> I've read there are two advantages of EXISTS. One is that EXISTS can
> perform faster than IN under certain circumstances. The other
> advantage I've heard is that there are some queries that cannot be done
> with IN, but can be done with EXISTS.
>
> Could someone post the reasoning behind performance gains?
>
> Could someone also post a good example of when only EXISTS will work?
>
> Thanks!
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon Mar 27 2000 - 21:59:55 CST