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(at)mascari(dot)com

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