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

also remember you set theory

select emp_no from emp
minus
select emp_no from bad_boys

will give the same results and *may* perform even faster

--
Niall Litchfield
Oracle DBA
Audit Commission UK
"joon" <joopark_at_home.com> wrote in message news:%6WD4.1762$W5.20940_at_news1.rdc1.ab.home.com...
> 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 Tue Mar 28 2000 - 08:29:53 CST