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(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
>