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.
>
>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
>
>
>
try:
 SELECT name FROM people
  WHERE EXISTS(SELECT 1 FROM states
                    WHERE name ~*'r' AND people.state = states.state)