sql - 如何优化此SQL Delete语句以提高速度(How to optimize this SQL Delete statement for faster speed)

Question

There is a faster way to run this SQL delete than this one:

(运行此SQL delete比此方法更快：)

DELETE FROM TABLE 
WHERE TABLE.key NOT IN (SELECT DISTINCT(MAIN_TABLE.key) FROM MAIN_TABLE) 

  ask by mister translate from so

Answer 1

You can prefer using not exists

(您可以选择使用not exists)

delete from TABLE t 
 where not exists ( select 0 from MAIN_TABLE m where m.key = t.key )

mostly preferable in performance point of view rather than not in .

(大多优选在视而非性能点not in 。)

I think this is because

(我认为这是因为)

NOT IN returns true for each non-matched value is found in a subquery, while

(对于在子查询中找到的每个不匹配值， NOT IN返回true，而)

NOT EXISTS is active only if non-matching row is found within the subquery.

(仅当在子查询中找到不匹配的行时， NOT EXISTS才处于活动状态。)