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

How about:

DELETE * FROM T1 LEFT JOIN T2 ON T1.PK <http://t1.pk/> = T2.FK<http://t2.fk/>
WHERE T2.FK IS NULL

Shaul

On Tue, Oct 20, 2009 at 2:37 PM, Shaul Dar <shauldar(at)gmail(dot)com> wrote:

> Hi,
>
> I have two large tables T1 and T2, such that T2 has a FK to T1 (i.e. T2.FK-->
> T1.PK, possibly multiple T2 rows may reference the same T1 row). I have
> deleted about 2/3 of table T2. I now want to delete all rows in T1 that are
> not referenced by T2, i.e. all rows in T1 that cannot join with (any row in)
> T2 on the condition T2.FK = T1.PK (the opposite of a join...)
>
> 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)
>
> What is an *efficient* way to do this?
> Thanks,
>
> -- Shaul
>
>