删除没有ID存在于另一个表中的所有行

Question

我想删除它有另一个表 例如没有现有的外键的所有行：

ID为＃20

table1 
+----+-------+ 
|id | data | 
+----+-------+ 
| 1 | hi | 
+----+-------+ 
| 2 | hi | 
+----+-------+ 
| 3 | hi | 
+----+-------+ 
| 4 | hi | 
+----+-------+ 
| 5 | hi | 
+----+-------+ 

table2 
+----+-------+ 
|a_id| data | 
+----+-------+ 
| 1 | hi | 
+----+-------+ 
| 20 | hi | 
+----+-------+ 
| 3 | hi | 
+----+-------+ 
| 40 | hi | 
+----+-------+ 
| 5 | hi | 
+----+-------+ 

的查询将删除行桌子上有40个。

我需要这样做，以便我可以建立与table1和table2的关系。

Answer 1

DELETE table2 
FROM table2 
     LEFT JOIN table1 
       ON table2.a_id = table1.id 
WHERE table1.id IS NULL 

Answer 2

归纳起来，有树的方式来删除多表

1. NOT IN(SELECT ...) - @someone（他已经删除了他的答案）

   Delete From Tab2 where ID not in (Select ID From Tab1) 
   

2. LEFT JOIN - @eggyal

   DELETE table2 
   FROM table2 LEFT JOIN table1 ON table2.a_id = table1.id 
   WHERE table1.id IS NULL 
   

3. NOT EXISTS

   DELETE 
   FROM table2 
   WHERE NOT EXISTS (
       SELECT 1 
       FROM table1 
       WHERE table1.id = table2.a_id 
   ) 
   

根据What's the difference between NOT EXISTS vs. NOT IN vs. LEFT JOIN WHERE IS NULL?，不同RDBMS不同地执行。