Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: optimal sql
"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:<NK2J9.296467$QZ.44251_at_sccrnsc02>...
> Not exists stops when it finds the first match and not in gets the whole
> list.
> Jim
> "Christoph Seidel" <chris666.seidel_at_gmx.de> wrote in message
> news:at1ufn$vjojo$1_at_ID-143718.news.dfncis.de...
> > Martin Doherty wrote:
> > > NOT IN becomes very inefficient for many values.
> > >
> > > try NOT EXISTS instead.
> >
> > hm, where the hell is the difference?
> >
> >
If I understand it right, pk column is a primary key in the second table as well. In this case, there is no difference between not in and not exists. BTW, in some cases the most efficient statment would be:
select * from t1
where t1.pk in
(select pk from t1
minus
select pk from t2)
If pk is indexed in both tables, subquery is very efficient because it uses index scan only. Received on Mon Dec 09 2002 - 13:06:16 CST