Inner join on delete query is that bad?

Question

I'm currently trying to delete a row from a MySQL table using C#.
I have two very basic tables: 'head_doc' and 'Doc_details'. Head_doc contains a field called id_doc. Doc_details has also a field called id_doc and various other fields.
I'm trying to delete all of the details within Doc_details for a particular id_doc that can be found in head_doc.
This is my query but I've been told that INNER JOIN on a DELETE query is bad programming. How can I change it?

DELETE h.*,d.* 
FROM head_doc h 
    INNER JOIN  
    doc_details d
    ON h.id_doc = d.id_doc
    WHERE h.id_doc= id_doc;

Probably duplicate of this famous question http://stackoverflow.com/questions/652770/delete-with-join-in-mysql — Pratik Joshi, May 25 '15 at 16:47

score 2 · Answer 1 · edited May 25 '15 at 17:07

If you're just interested in getting rid of records in doc_details that don't have a corresponding parent record (aka orphaned records), then do this (this can be avoided by adding foreign key constrains. See note below):

delete
from doc_details
where id_doc not in (
   select id_doc from head_doc
)

However, if you want to get rid of all records in both tables that have a specific id, in the interest of clarity, it might be a good idea to break out your delete statements:

delete from doc_details where id_doc = <value>
delete from doc_head where id_doc = <value>

NOTE: If you have foreign key constraints set up on a child table, and you want to delete a record out of the parent table, the database engine will prevent you from deleting the record out of the parent table if the children records aren't removed first. Whenever possible, setting up foreign key constrains is a good idea to ensure that you don't have orphaned records in a child table when parent records are deleted.

To delete "orphaned" rows, we could use an ant-join pattern rather than a `NOT IN (subquery)`. e.g. **`DELETE d.* FROM doc_details d LEFT JOIN head_doc h ON h.id_doc = d.id_doc WHERE h.id_doc IS NULL`**. (The `.*` isn't required, but we usually write this as a SELECT first, to make sure we're identifying the rows we want to delete, and we convert that to a delete statement by replacing `SELECT` with `DELETE`.) — spencer7593, May 25 '15 at 17:45
Good point! The ant-join pattern has proven to be much more efficient (faster) in my experience, specifically when used in select statements. — Hexadecimal, May 25 '15 at 19:04

score 1 · Accepted Answer · answered May 25 '15 at 18:11

No, it's not bad practice to use a join operation in a DELETE statement. It's a common pattern.

I'm not sure whose told you that it was "bad programming", if they have any rationale for telling you that.

For delete rows from more than one table, there is a possibility that MySQL may perform the operations in an order which causes foreign key constraints to be violated (with InnoDB tables).

For example, MySQL may attempt to delete a row from head_doc before it deletes the related child rows from doc_details.

If you use a multiple-table DELETE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. In this case, the statement fails and rolls back. Instead, you should delete from a single table and rely on the ON DELETE capabilities that InnoDB provides to cause the other tables to be modified accordingly.

Reference: http://dev.mysql.com/doc/refman/5.5/en/delete.html

Or, we can sometimes workaround that issue by using separate statements to delete from each table.

We note that the query in the question will only only remove rows from head_doc that have a related child row in doc_details, because of the inner join operation. That will leave row(s) in head_doc if there aren't any child rows. That's not necessarily bad programming. But it is a bit odd.

Given that you want to remove rows from the two tables, you probably want an outer join, so you delete rows from head_doc even when no matching child rows exist in doc_detais

 DELETE d.* 
      , h.*
   FROM head_doc h
   LEFT
   JOIN doc_details d 
     ON d.id_doc = h.id_doc 
  WHERE h.id_doc =  ?

Given that you have id_doc in both tables, a simpler pattern would be to use two separate DELETE statements, first delete from the child table, then the parent:

 DELETE FROM doc_details WHERE id_doc = ?
 DELETE FROM head_doc    WHERE id_doc = ?

In cases where the rows to be removed from head_doc are identified by some other condition than on the id_doc column, on columns that are not available in doc_details, we can use a JOIN operation to identify the rows in the child table that should be removed

e.g. first, delete rows from the child table (using a JOIN operation)

 DELETE d.*
   FROM head_doc h
   JOIN doc_details d 
     ON d.id_doc = h.id_doc 
  WHERE h.somecol  < ?
    AND h.othercol = ?

Then, delete from the parent table:

 DELETE h.*
   FROM head_doc h
  WHERE h.somecol  < ?
    AND h.othercol = ?

In summary, using a JOIN operation in a DELETE is not necessarily "bad programming". There are some caveats we need to be aware of. Sometimes, using a join operation is the best approach. It really depends on what you want to achieve.

Since I am not very skilled yet and I want to make as easiest and legible as possible, I am about to ask another stupid question. Is that possible to write two separate delete queries using an AND keyword? Something like: DELETE FROM doc_details WHERE doc_details .id_doc = 28 AND DELETE FROM head_doc WHERE head_doc.id_doc = 28 — Nerd Nord, May 26 '15 at 09:34
@NerdNord: No, that syntax doesn't work. Two separate statements. Or, you are using InnoDB, and there's a foreign key relationship, define the constraint with `ON DELETE CASCADE` option, and just run a delete statement on the parent table. (We use this pattern for tables that are "closely related", where the child table is really "part of" the same entity (repeating attribute, or line items of an order, where we want to handle the two tables together as an "entity".) — spencer7593, May 26 '15 at 14:37

Inner join on delete query is that bad?

2 Answers2