Received: from netcom.com (zalman@netcom8.netcom.com [199.183.9.108]) by hub.org (8.9.3/8.9.3) with ESMTP id DAA13433 for ; Tue, 2 Nov 1999 03:42:45 -0500 (EST) (envelope-from zalman@netcom.com) Received: (from zalman@localhost) by netcom.com (8.9.3/8.9.3) id AAA23090; Tue, 2 Nov 1999 00:42:41 -0800 (PST) From: Zalman Stern Message-Id: <199911020842.AAA23090@netcom.com> Subject: Re: [SQL] query with subquery abnormally slow? To: osk@hem.passagen.se (Oskar Liljeblad) Date: Tue, 2 Nov 1999 00:42:41 -0800 (PST) Cc: pgsql-sql@postgreSQL.org In-Reply-To: <19991101194707.A7034@oskar> from "Oskar Liljeblad" at Nov 1, 99 07:47:07 pm X-Mailer: ELM [version 2.4 PL25] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit I inadvertently deleted Oskar's message where he described what he is trying to do at a higher level, but I don't think I'm completely missing the point with the following: The table looks like so: select * from test; [ ssmldb=> select * from test; package |artist |song ----------------------+-------------------+----------------- Surf Comp |Mermen |Pulpin Surf Comp |Bambi Molesters |Tremor Surf Comp |The Squares |Squaranoid Dumb Loud Hollow Twang|Bambi Molesters |Point Break Dumb Loud Hollow Twang|Bambi Molesters |Glider Songs of the Cows |Mermen |Songs of the Cows Surfmania |The Aqua Velvets |Surfmania Surf Comp 2 |Mermen |Slo Mo H50 Surf Comp 2 |Los Straightjackets|Caveman ] select t2.* from test t1, test t2 where t1.package = t2.package and t1.artist = 'Mermen' and t2.artist != 'Mermen'; [ package |artist |song -----------+-------------------+---------- Surf Comp |Bambi Molesters |Tremor Surf Comp |The Squares |Squaranoid Surf Comp 2|Los Straightjackets|Caveman (3 rows) ] The query above shows all songs by a different artist that share an album with a song by the artist in question. It however omits the songs by the artist in question. To get those back, you could try: select distinct t1.* from test t1, test t2 where t1.package = t2.package and ((t1.artist = 'Mermen' and t2.artist != 'Mermen') or (t1.artist != 'Mermen' and t2.artist = 'Mermen')); or use a union clause: select t1.* from test t1, test t2 where t1.package = t2.package and (t2.artist = 'Mermen' and t1.artist != 'Mermen') union select t3.* from test t3, test t4 where t3.package = t4.package and (t3.artist = 'Mermen' and t4.artist != 'Mermen') ; I don't know how these do for speed as I don't care to create a big table and indices and all that, but they do not use EXITS and it seems with suitable indices they should be fairly fast. -Z-