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)