Re: Using the IN clauise

On Fri, 2002-10-25 at 14:40, terry@ashtonwoodshomes.com wrote:
> My post didn't go through the first time, so here goes again:
> >
> >
> > I thought I read somewhere that it was better to avoid using
> > the IN clause (at least when that means doing a subselect)
> > for efficiency reasons, but I cannot find it on the website now.
> >
> > Does anyone know where that is?
> >

Do a search on the interactive docs for IN or EXISTS, I think it's
mentioned there.  Either way it is generally recommended to use the
EXISTS grammer rather than the IN for performance reasons. When in
doubt, explain analyze is your friend.


> > And can someone confirm the following:
> >
> > This:
> > SELECT t1.f1
> > FROM t1
> > WHERE t1.f1 IN (select f1 from t2 AS t2 where t2.f1 = t1.f1
> > AND t2.f2 = 'v1')
> >
> > is generally slower to run then:
> > SELECT t1.f1
> > FROM t1, t2
> > WHERE t1.f1 = t2.f1
> >   AND t2.f3 = 'v1'
> >

I would believe it to be true, though depending on your table / data
structure I can't say 100% percent true.  Try running explain analyze on
both queries and see what you return.

> > Thanks
> >
> > Terry Fielder
> > Network Engineer
> > Great Gulf Homes / Ashton Woods Homes
> > terry@greatgulfhomes.com
> >

Robert Treat