MySQL DELETE JOIN: Syntax and Examples

A MySQL DELETE JOIN uses a join to find rows to remove from one or more tables. The tables in the join determine which rows match; the aliases listed after DELETE determine which tables are actually changed.

For single-table deletion syntax, see MySQL DELETE. This guide uses the MySQL JOIN syntax to select related rows.

MySQL DELETE JOIN syntax

To delete from one table while using another table to find matches, list only the target alias after DELETE:

DELETE target_alias
FROM target_table AS target_alias
JOIN filter_table AS filter_alias
    ON join_condition
WHERE row_condition;

The joined table can filter the target rows without being deleted. To delete from more than one table, list multiple target aliases after DELETE.

Delete rows matched by an INNER JOIN

Suppose an application stores orders and their line items in separate tables. The following example removes line items for cancelled orders while leaving the orders table unchanged:

CREATE TABLE orders_demo (
    order_id INT PRIMARY KEY,
    status VARCHAR(20) NOT NULL
);

CREATE TABLE order_items_demo (
    item_id INT PRIMARY KEY,
    order_id INT NOT NULL,
    item_name VARCHAR(80) NOT NULL
);

INSERT INTO orders_demo (order_id, status) VALUES
    (101, 'cancelled'),
    (102, 'paid'),
    (103, 'cancelled');

INSERT INTO order_items_demo (item_id, order_id, item_name) VALUES
    (1, 101, 'Keyboard'),
    (2, 101, 'Mouse'),
    (3, 102, 'Notebook'),
    (4, 103, 'Desk lamp');

First preview the line items that match the deletion condition:

SELECT i.item_id, i.order_id, i.item_name
FROM order_items_demo AS i
JOIN orders_demo AS o
    ON o.order_id = i.order_id
WHERE o.status = 'cancelled'
ORDER BY i.item_id;

The preview returns:

+---------+----------+------------+
| item_id | order_id | item_name  |
+---------+----------+------------+
|       1 |      101 | Keyboard   |
|       2 |      101 | Mouse      |
|       4 |      103 | Desk lamp  |
+---------+----------+------------+

When the preview is correct, delete only from order_items_demo:

DELETE i
FROM order_items_demo AS i
JOIN orders_demo AS o
    ON o.order_id = i.order_id
WHERE o.status = 'cancelled';

The i alias after DELETE is the target. The orders_demo table is used to filter rows, but its orders are not deleted. Check the remaining line items:

SELECT item_id, order_id, item_name
FROM order_items_demo
ORDER BY item_id;

Only the line item for the paid order remains:

+---------+----------+------------+
| item_id | order_id | item_name  |
+---------+----------+------------+
|       3 |      102 | Notebook   |
+---------+----------+------------+

Delete unmatched rows with LEFT JOIN

A LEFT JOIN can identify rows in one table that have no match in another. For example, to remove orphaned child rows from a legacy table without an enforced foreign key:

DELETE child
FROM child_table AS child
LEFT JOIN parent_table AS parent
    ON parent.parent_id = child.parent_id
WHERE parent.parent_id IS NULL;

The WHERE ... IS NULL condition selects rows with no matching parent. Preview the same join with SELECT first. If a foreign key already prevents orphan rows, investigate why data is inconsistent instead of using this query blindly.

Important DELETE JOIN limits and foreign keys

• Keep a restrictive WHERE clause. Without one, the joined rows can select far more targets than intended. Run an equivalent SELECT and inspect its result before deleting.
• Multi-table DELETE has no ORDER BY or LIMIT. Those clauses are available for single-table DELETE, but not multi-table deletes. For large deletions, select and process a controlled set of keys in batches.
• Be careful when deleting multiple foreign-key-related tables. With InnoDB, the optimizer may process target tables in an order that conflicts with the foreign-key relationship, causing the statement to fail. Prefer deleting from one table and using ON DELETE CASCADE where that behavior is appropriate, or delete dependent rows in a deliberate order.
• Use a transaction for reversible work. With transactional tables such as InnoDB, you can run the delete inside a transaction, verify the result, and COMMIT or ROLLBACK. See MySQL transactions.

For the full syntax, see the MySQL 8.4 Reference Manual: DELETE Statement.