Re: Subquery with IN or EXISTS

Carl van Tast <vanTast(at)Pivot(dot)at> wrote in message news:<ngl4rtstb3jljr86v3hei05f74s70bmg3v(at)4ax(dot)com>...
> Hi A.,
>
> On 26 Sep 2001 07:24:41 -0700, anssiman(at)my-deja(dot)com (A. Mannisto)
> wrote:
>
> >Hello,
> >
> >does anybody know why this:
> >SELECT * FROM tab WHERE col1 IN (SELECT col2 FROM TAB2)
> >
> >equals this:
> >SELECT * FROM tab WHERE EXISTS (SELECT col2 FROM TAB2 WHERE col1 =
> >col2)
> >
> >but this:
> >SELECT * FROM tab WHERE col1 IN (SELECT col2 FROM TAB2 WHERE
> >col3='huu')
> >
> >equals _NOT_ this:
> >SELECT * FROM tab WHERE EXISTS (SELECT col2 FROM TAB2 WHERE col1 =
> >col2 AND col3='huu')
> >
> >E.g. resultset is not the same in last two statements.
> >Can I get same set as IN statement somehow using EXISTS (performance
> >issue)?
>
> I cannot reproduce your problem, results are equal here with
> PostgreSQL 7.1.3. Could you post your CREATE TABLE and INSERT
> statements?
>
> Re performance: There's more than one way to do it. (Where did I hear
> this before? ;-)) You might try:
>
> SELECT tab.* FROM tab, tab2 WHERE tab.col1 = tab2.col2;
>
> or SELECT DISTINCT ... , if col2 is not unique in tab2.
>
> Kind regards,
> Carl van Tast

Sorry,
I mixed up names of the columns of different tables.
There was columns of the same name in both tables and
my statement uses the wrong one (of course). Now it's OK!

Thanks for advise!