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

In response to Shaul Dar :
> 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,

Maybe this one:

(my id is your pk):

delete from t1 where t1.id in (select t1.id from t1 left join t2 using
(id) where t2.id is null);

Try it, and/or use explain for both versions and see which which is
faster.

Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)