A suggestion is to use a left outer join and then test for rows with a right-hand null value (ones where the join failed).
The following example creates 2 tables, t1 and t2. t1 has related records in t2 and the relation is indicated by t1.rel->t2.id create table t1 ( id integer, rel integer, label varchar(10) ); create table t2 ( id integer, label varchar(10) ); insert into t1 (id, rel, label) values (1, 1, 'bob'); insert into t1 (id, rel, label) values (2, 2, 'sam'); insert into t1 (id, rel, label) values (3, 0, 'alice'); insert into t2 (id, label) values (1, 'martin'); insert into t2 (id, label) values (2, 'gwen'); -- this shows you all the records select t1.*, t2.* from t1 left outer join t2 on (t1.rel = t2.id); -- this shows you those where the inner join fails (your where X NOT IN (y) stuff) -- the result should be where t1.id=3 because it has a t1.rel of 0 therefore no related -- record in t2 select t1.*, t2.* from t1 left outer join t2 on (t1.rel = t2.id) where t2.id is null Toby -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Graham Leggett Sent: 01 December 2003 17:16 To: [EMAIL PROTECTED] Subject: [GENERAL] Postgresql sql query - selecting rows outside a join Hi all, I am trying to do a query that returns all rows that are _not_ part of a join, and so far I cannot seem to find a query that doesn't take 30 minutes or more to run. The basic query is "select * from tableA where tableA_id NOT IN (select tableA_id from tableB)". Is there a more efficient way of doing this? Regards, Graham -- ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
