From: | Zalman Stern <zalman(at)netcom(dot)com> |
---|---|
To: | osk(at)hem(dot)passagen(dot)se (Oskar Liljeblad) |
Cc: | pgsql-sql(at)postgreSQL(dot)org |
Subject: | Re: [SQL] query with subquery abnormally slow? |
Date: | 1999-11-02 08:42:41 |
Message-ID: | 199911020842.AAA23090@netcom.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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-
From | Date | Subject | |
---|---|---|---|
Next Message | Klein, Robert | 1999-11-02 15:34:55 | Redhat 6.0 Link Error: Undefined Reference to crypt |
Previous Message | Roomi | 1999-11-02 07:21:17 | ERROR: btree: lost page |