Delete from multiple tables in one statement

Question

Using MySQL, I am trying to delete multiple records from multiple tables at once. Initially I thought I could do this:

DELETE t1, t2
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.table1_id
JOIN table3 t3 ON t1.id = t3.table1_id
WHERE t1.id IN (?,?,?,?);

However, if there are no existing records in table2, should I change JOIN to LEFT JOIN? Also, if I delete only two or three records from the eight tables (2x2x2x2x2x2x2x2), will this cause a delay?

Answer 1

Yes, changing the join on table2 to left join will achieve the effect you want. Rows in table1 that belong to the list and table3 will be deleted, regardless of whether they also exist in table2. At the same time, possible matching lines will also be deleted.

delete t1, t2
from table1 t1
left join table2 t2 on t1.id = t2.table1_id
inner join table3 t3 on t1.id = t3.table1_id
where t1.id in (?, ?, ?, ?);

I recommend rewriting the join on table3 to the exists condition. This makes the intent of the query clearer and may perform better, especially if there is an index on table3(table1_id):

delete t1, t2
from table1 t1
left join table2 t2 on t1.id = t2.table1_id
where 
    t1.id in (?, ?, ?, ?)
    and exists (select 1 from table3 t3 where t3.table1_id = t1.id)