Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f8QN48h11478 for ; Wed, 26 Sep 2001 19:04:08 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id f8QMjr160433 for pgsql-sql@postgresql.org; Wed, 26 Sep 2001 18:45:53 -0400 (EDT) (envelope-from news) From: Carl van Tast X-Newsgroups: comp.databases.postgresql.sql Subject: Re: Subquery with IN or EXISTS Date: Thu, 27 Sep 2001 00:45:43 +0200 Organization: Hub.Org Networking Services (http://www.hub.org) Lines: 39 Message-ID: References: <8b9752ea.0109260624.5b0d2c20@posting.google.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Complaints-To: scrappy@hub.org X-Newsreader: Forte Agent 1.8/32.548 To: pgsql-sql@postgresql.org X-Archive-Number: 200109/234 X-Sequence-Number: 4916 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