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@campbell-lange.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
>