Re: Finding rows in table T1 that DO NOT MATCH any row in table T2

[Melton Low <softw.db@xxxxxxxxx>]

How about

DELETE FROM T1 WHERE T1.PK IN

(SELECT T1.PK FROM T1 EXCEPT SELECT T2.FK FROM T2);

Mel

On Tue, Oct 20, 2009 at 7:59 AM, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:

Shaul Dar <shauldar@xxxxxxxxx> writes:
> I assume this will work but will take a long time:

> DELETE * FROM T1 where T1.PK NOT IN
> (SELECT T1.PK FROM T1, T2 where T1.PK = T2.FK)

Well, yeah, but it's unnecessarily inefficient --- why not just

DELETE FROM T1 where T1.PK NOT IN

(SELECT T2.FK FROM T2)

However, that still won't be tremendously fast unless the subselect fits
in work_mem.  As of 8.4 this variant should be reasonable:

DELETE FROM T1 where NOT EXISTS
(SELECT 1 FROM T2 where T1.PK = T2.FK)

Pre-8.4 you should resort to the "left join where is null" trick,
but there's no need to be so obscure as of 8.4.

                       regards, tom lane

--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance