Subquery with IN or EXISTS

From: anssiman(at)my-deja(dot)com (A(dot) Mannisto)
To: pgsql-sql(at)postgresql(dot)org
Subject: Subquery with IN or EXISTS
Date: 2001-09-26 14:24:41
Message-ID: 8b9752ea.0109260624.5b0d2c20@posting.google.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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)?

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Frederick Klauschen 2001-09-26 15:14:32 is it possible to get the number of rows of a table?
Previous Message Thurstan R. McDougle 2001-09-26 12:26:52 Re: A simple join question that may stump you