Summary: in this tutorial, we’ll show you how to delete data from multiple tables by using MySQL DELETE JOINstatement.
In the previous tutorial, we showed you several ways to delete records from multiple tables by using:A single DELETEstatement on multiple tables.
A single DELETEstatement on multiple tables where the child tables have ON DELETE CASCADE referential actions for the foreign keys.
This tutorial introduces to you a more flexible way to delete data from multiple tables by using INNER JOIN or LEFT JOIN clause in the DELETEstatement.
It is easier to follow this tutorial if you already have some knowledge of the INNER JOIN and LEFT JOIN clauses.
MySQL DELETE JOIN with INNER JOIN
You often use the INNER JOIN clause in the SELECT statementto select records from a table that have corresponding records in other tables. To make it more convenient, MySQL also allows you to use the INNER JOINclause with the DELETEstatement to delete records from a table and also the corresponding records in other tables e.g., to delete records from both T1and T2tables that meet a particular condition, you use the following statement:DELETE T1, T2 FROM T1 INNER JOIN T2 ON T1.key = T2.key WHERE condition
Notice that you put table names T1and T2between DELETEand FROM. If you omit the T1table, the DELETEstatement only deletes records in the T2table, and if you omit the T2table, only records in the T1table are deleted.
The join condition T1.key = T2.key specifies the corresponding records in the T2table that need be deleted.
The condition in the WHEREclause specifies which records in the T1 and T2 that need to be deleted.
MySQL DELETE JOIN with INNER JOIN example
Let’s take a look at the following diagram.
Each office has one or more employees, however each employee only belongs to on office.
Suppose you want to delete the office with officeCode 5 and you don’t update or delete the officeCodecolumn in the employeestable, the data in the employees table would be invalid. To delete the office with officeCode 5 and also employee records that belong to the office, you can use the DELETEstatement with the INNER JOIN clause.
Let’s check the offices and employees data before deleting the data.SELECT * FROM offices WHERE officeCode = 5
SELECT * FROM employees WHERE officeCode = 5
Now, you can delete records associated with officeCode 5 from both officesand employeestables:DELETE offices, employees FROM offices INNER JOIN employees ON employees.officeCode = employees.officeCode WHERE offices.officeCode = 5
If you use the SELECTstatements above to query office and employee data with officeCode5 in the officesand employeestables again, you will not see any row returned.
MySQL DELETE JOIN with LEFT JOIN
You often use LEFT JOINclause in the SELECTstatement to find records that exist in the left table and does not have corresponding records in the right table. You can also use the LEFT JOIN clause in the DELETEstatement to delete record in a table (left table) that does not have corresponding record in another table (right table).
The following syntax illustrates how to use DELETEstatement with LEFT JOIN clause to delete records from T1table that does not have corresponding records in the T2table:DELETE T1 FROM T1 LEFT JOIN T2 ON T1.key = T2.key WHERE T2.key IS NULL
Note that you only put T1table after the DELETEkeyword, not both T1and T2tables like you did with the INNER JOIN clause.
MySQL DELETE JOIN with LEFT JOIN example
Let’s see the following database diagram:
Each customer has zero or more orders. However, each order belongs to one and only one customer.
You can use DELETEstatement with LEFT JOIN clause to clean up our customers master data. The following statement removes customers who do not have any order:DELETE customers FROM customers LEFT JOIN orders ON customers.customerNumber = orders.customerNumber WHERE orderNumber IS NULL;
Now, if we query customers who have not ordered any product by using the following SELECTLEFT JOINquery:SELECT c.customerNumber, c.customerName, orderNumber FROM customers c LEFT JOIN orders o ON c.customerNumber = o.customerNumber WHERE orderNumber IS NULL;
You will not see any row returned. It means you have successfully deleted customers who have not ordered any products.
In this tutorial, You have learn how to use the MySQL DELETE JOIN with INNER JOIN and LEFT JOIN to delete data from multiple tables.