DELETE with JOIN

MySQL allows you to delete rows from one or more tables using a JOIN. This is useful when the rows to delete are identified based on data in a related table, or when you need to delete matching rows from multiple tables simultaneously.

Single-Table DELETE with JOIN Filter

You can join another table to filter which rows to delete from the primary table. Only the primary table (listed after DELETE) has its rows removed; the joined table is used only for the filter condition.

Multi-Table DELETE

By listing multiple table aliases after DELETE, you can remove rows from several tables in a single statement. This is useful for maintaining referential integrity when foreign key constraints are not in place.

List the aliases of tables to delete from after the DELETE keyword
Define the FROM and JOIN clauses as usual
Only rows in the listed aliases are deleted
The operation is atomic within a transaction

Example

-- Delete orders AND their items for cancelled customers
DELETE o, oi
FROM orders o
INNER JOIN order_items oi ON oi.order_id = o.id
INNER JOIN customers c ON c.id = o.customer_id
WHERE c.status = 'cancelled';

SQL

Full Editor

Pro Tip

Always alias table names in multi-table DELETE for clarity.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned