Обсуждение: using EXISTS instead of IN: how?

Поиск
Список
Период
Сортировка

using EXISTS instead of IN: how?

От
Rory Campbell-Lange
Дата:
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>

Re: using EXISTS instead of IN: how?

От
Csaba Nagy
Дата:
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
>

Re: using EXISTS instead of IN: how?

От
DeJuan Jackson
Дата:
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)

Re: using EXISTS instead of IN: how?

От
Mike Mascari
Дата:
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@mascari.com





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

Re: using EXISTS instead of IN: how?

От
Felipe Schnack
Дата:
  Why using IN is not advisable???
On 22 Jul 2003 18:36:10 +0200
Csaba Nagy <nagy@ecircle-ag.com> wrote:

> 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
> >
>
>
>
> ---------------------------(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


--

 /~\ The ASCII        Felipe Schnack (felipes@ritterdosreis.br)
 \ / Ribbon Campaign  Analista de Sistemas
  X  Against HTML     Cel.: 51-91287530
 / \ Email!           Linux Counter #281893

Centro Universitário Ritter dos Reis
http://www.ritterdosreis.br
ritter@ritterdosreis.br
Fone: 51-32303341

Re: using EXISTS instead of IN: how?

От
Csaba Nagy
Дата:
Actually, even better:

select name
from people p, states s
where p.state = s.name
  and p.state ~* 'r';

Cheers,
Csaba.


On Tue, 2003-07-22 at 18:36, Csaba Nagy wrote:
> 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
> >
>
>
>
> ---------------------------(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
>

Re: using EXISTS instead of IN: how?

От
Mike Mascari
Дата:
Felipe Schnack wrote:
>   Why using IN is not advisable???

http://www.postgresql.org/docs/faqs/FAQ.html#4.22

But I believe Tom has fixed this for the upcoming 7.4.

Mike Mascari
mascarm@mascari.com