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

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Shaul Dar <shauldar(at)gmail(dot)com> 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>