Delete all rows that do not have an identifier existing in another table.

Question

I want to delete all rows that do not have an existing foreign key in another table Example:

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    |
+----+-------+

the query will delete rows with identifiers # 20 and 40 on table2.

I need to do this so that I can establish a relationship with table 1 and table2.

Answer 1

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

Answer 2

To summarize, there are several ways to delete multiple tables.

• NOT IN(SELECT ...) - @someone (he deleted his answer)

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

• LEFT JOIN - @eggyal

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

• NOT EXISTS

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

According to What is the difference between NOT EXISTS and NOT IN vs. LEFT JOIN WHERE NULL? Different RDBMSs work differently.