Re: using EXISTS instead of IN: how?

Why using IN is not advisable???
On 22 Jul 2003 18:36:10 +0200
Csaba Nagy <nagy(at)ecircle-ag(dot)com> 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
> > <rory(at)campbell-lange(dot)net>
> > <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 (felipes(at)ritterdosreis(dot)br)
\ / 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
ritter(at)ritterdosreis(dot)br
Fone: 51-32303341