Re: using EXISTS instead of IN: how?

Actually, even better:

select name
from people p, states s
where p.state = s.name
  and p.state ~* 'r';

Cheers,
Csaba.


On Tue, 2003-07-22 at 18:36, Csaba Nagy 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@campbell-lange.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
>