I would use an outer join and check only those null-value records in the right table with id's referencing table A
Sample query: select a.*,b.* from a left outer join b on a.id = b.a_id -- assuming a_id is my referencing column to a where b.id is null; This will yield all columns in table a which has a null value on table b This is just from the top of my head, just a concept, I might have some syntax error. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wes Sent: Wednesday, January 11, 2006 11:51 PM To: Postgresql-General Subject: [GENERAL] Finding orphan records I'm trying to find/delete all records in table A that are no longer referenced by tables B or C. There are about 4 million records in table A, and several hundred million in tables B and C. Is there something more efficient than: select address_key, address from addresses where ( not exists(select 1 from B where BField=addresses.address_key limit 1) ) and ( not exists(select 1 from C where CField=addresses.address_key limit 1) ) Of course, all fields above are indexed. There are foreign key references in B and C to A. Is there some way to safely leverage that? Wes ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.371 / Virus Database: 267.14.16/225 - Release Date: 1/9/2006 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
