Received: from dfw-ix5.ix.netcom.com (dfw-ix5.ix.netcom.com [206.214.98.5]) by hub.org (8.9.3/8.9.3) with ESMTP id RAA13204 for ; Thu, 19 Aug 1999 17:19:17 -0400 (EDT) (envelope-from hamid@emarq.com) Received: (from smap@localhost) by dfw-ix5.ix.netcom.com (8.8.4/8.8.4) id QAA27241 for ; Thu, 19 Aug 1999 16:18:41 -0500 (CDT) Received: from unknown(207.230.254.117) by dfw-ix5.ix.netcom.com via smap (V1.3) id rma027218; Thu Aug 19 16:18:16 1999 From: "Hamid Khoshnevis" To: Subject: RE: [SQL] Using intersect in subquery Date: Thu, 19 Aug 1999 14:28:58 -0700 Message-ID: <000b01beea89$d8d7e800$810a0a0a@local.dom> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0 Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4 In-Reply-To: I re-implemented the intersect using sub-selects. It worked fine. Then I tried Herouth's solution of: SELECT * FROM Table1 WHERE KeyField in ( SELECT Keyfield2 FROM Table2, Table3 WHERE Keyfield2 = Keyfield3 ); This one is blazingly fast (by a factor of 4) compared to sub-select. Moral of the story IMHO: Use flat select with a lot of joins vs. intersects or sub-selects to get fast response (anyone?). hamid > -----Original Message----- > From: Herouth Maoz [mailto:herouth@oumail.openu.ac.il] > Sent: Wednesday, August 18, 1999 7:22 AM > To: Hamid Khoshnevis; pgsql-sql@postgreSQL.org > Subject: Re: [SQL] Using intersect in subquery > > > At 16:51 +0300 on 18/08/1999, Tom Lane wrote: > > > > Bruce Momjian writes: > > > Try 6.5. We fixed some Intersect stuff. > > >> > > >> select * from Table1 > > >> where KeyField1 in > > >> (select KeyField2 from Table2 where somecondition > > >> intersect > > >> select KeyField3 from Table3 where somecondition); > > >> > > >> (I believe) I am running PG 6.4. The INTERSECT by itself works, but > >not in > > >> a subquery. > > >> > > > > No, it still won't work --- the current grammar specifies SubSelect not > > select_clause as the kind of select you can put inside an expression. > > Not sure what it would take to fix this; I have a feeling that just > > changing the grammar wouldn't be good enough :-(. Better add it to the > > TODO list: > > * Support UNION/INTERSECT/EXCEPT in sub-selects > > In the meantime I suppose changing the query might work. Intersect should > return only the rows which are returned from both tables, but since we are > talking about only one field here, it may be convenient to just do an > internal join? > > SELECT * FROM Table1 > WHERE KeyField in > ( SELECT Keyfield2 > FROM Table2, Table3 > WHERE Keyfield2 = Keyfield3 > ); > > This is not equivalent in the case of a NULL, but in all other cases, I > believe it is. > > Or maybe the following is more efficient (and clear?): > > SELECT * FROM Table1 > WHERE EXISTS > ( SELECT * > FROM Table2 > WHERE KeyField2 = KeyField > ) > AND EXISTS > ( SELECT * > FROM Table3 > WHERE KeyField3 = KeyField > ); > > Herouth > > -- > Herouth Maoz, Internet developer. > Open University of Israel - Telem project > http://telem.openu.ac.il/~herutma > >