Why using IN is not advisable??? On 22 Jul 2003 18:36:10 +0200 Csaba Nagy <[EMAIL PROTECTED]> wrote:
> You should use something like: > > SELECT > name > FROM > people p > WHERE > exists ( > SELECT > 1 > FROM > states > WHERE > name = p.state > ) > AND state ~* 'r'; > > On Tue, 2003-07-22 at 18:28, Rory Campbell-Lange wrote: > > I have been informed that at present (postgres 7.3.2) using IN is not > > advised, and I should replace it with EXISTS. I can't seem to get it to > > work. > > > > I've tried replacing (example): > > > > SELECT > > name > > FROM > > people > > WHERE > > state IN ( > > SELECT > > id > > FROM > > states > > WHERE > > name ~* 'r' > > ); > > > > with > > > > SELECT > > name > > FROM > > people > > WHERE > > exists ( > > SELECT > > 1 > > FROM > > states > > WHERE > > name ~* 'r' > > ); > > > > However the second example simply finds all records in people. > > > > Thanks for any help, > > Rory > > > > -- > > Rory Campbell-Lange > > <[EMAIL PROTECTED]> > > <www.campbell-lange.net> > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 9: the planner will ignore your desire to choose an index scan if your > > joining column's datatypes do not match > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match -- /~\ The ASCII Felipe Schnack ([EMAIL PROTECTED]) \ / Ribbon Campaign Analista de Sistemas X Against HTML Cel.: 51-91287530 / \ Email! Linux Counter #281893 Centro Universitário Ritter dos Reis http://www.ritterdosreis.br [EMAIL PROTECTED] Fone: 51-32303341 ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
