pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: anssiman@my-deja.com
To: pgsql-sql@postgresql.org
Subject: Re: Subquery with IN or EXISTS
Date: 27 Sep 2001 02:49:22 -0700
Message-ID: <8b9752ea.0109270149.21bdd725@posting.google.com> (raw)
References: <8b9752ea.0109260624.5b0d2c20@posting.google.com>
	<ngl4rtstb3jljr86v3hei05f74s70bmg3v@4ax.com>

Carl van Tast <vanTast@Pivot.at> wrote in message news:<ngl4rtstb3jljr86v3hei05f74s70bmg3v@4ax.com>...
> Hi A.,
> 
> On 26 Sep 2001 07:24:41 -0700, anssiman@my-deja.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!



view thread (3+ messages)

Message-ID: <8b9752ea.0109270149.21bdd725@posting.google.com>
Permalink:  ../8b9752ea.0109270149.21bdd725@posting.google.com/
Also on:    postgresql.org/message-id/8b9752ea.0109270149.21bdd725@posting.google.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: anssiman@my-deja.com
  Subject: Re: Subquery with IN or EXISTS
  In-Reply-To: <8b9752ea.0109270149.21bdd725@posting.google.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox