Re: using EXISTS instead of IN: how?

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.

...

>
>     SELECT
>         name
>     FROM
>         people
>     WHERE
>         exists (
>             SELECT
>                 1
>             FROM
>                 states
>             WHERE
>                 name ~* 'r'
>         );

You should correlate the subquery with the outer query:

SELECT name
FROM people
WHERE EXISTS (
 SELECT 1
 FROM states
 WHERE people.state = states.id AND
 states.name ~* 'r'
);

But I don't see why you just don't use a join:

SELECT people.name
FROM people, states
WHERE people.state = states.id AND
states.name ~* 'r';

Hope that helps,

Mike Mascari
mascarm@mascari.com





>
> However the second example simply finds all records in people.
>
> Thanks for any help,
> Rory
>