Re: using EXISTS instead of IN: how?

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
>