mysql table multiple join_MySQL DELETE JOIN语句

Summary: in this tutorial, we’ll show you how to delete data from multiple tables by usingMySQLDELETEJOINstatement.

In the previous tutorial, we showed you several ways to delete records from multiple tables by using:A singleDELETEstatement on multiple tables.

A singleDELETEstatement on multiple tables where the child tables haveON DELETE CASCADEreferential actions for the foreign keys.

This tutorial introduces to you a more flexible way to delete data from multiple tables by usingINNER JOINorLEFT JOINclause in theDELETEstatement.

It is easier to follow this tutorial if you already have some knowledge of theINNER JOINandLEFT JOINclauses.

MySQL DELETE JOIN with INNER JOIN

You often use theINNER JOINclause in theSELECT statementto select records from a table that have corresponding records in other tables. To make it more convenient, MySQL also allows you to use theINNER JOINclause with theDELETEstatement to delete records from a table and also the corresponding records in other tables e.g., to delete records from bothT1andT2tables that meet a particular condition, you use the following statement:

DELETET1,T2FROMT1INNERJOINT2ONT1.key=T2.keyWHEREcondition

Notice that you put table namesT1andT2betweenDELETEandFROM. If you omit theT1table, theDELETEstatement only deletes records in theT2table, and if you omit theT2table, only records in theT1table are deleted.

The join conditionT1.key = T2.keyspecifies the corresponding records in theT2table that need be deleted.

The condition in theWHEREclause specifies which records in theT1andT2that 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 withofficeCode 5and you don’t update or delete theofficeCodecolumn in theemployeestable, the data in the employees table would be invalid. To delete the office withofficeCode 5and also employee records that belong to the office, you can use theDELETEstatement with theINNER JOINclause.

Let’s check the offices and employees data before deleting the data.

SELECT*FROMofficesWHEREofficeCode=5

SELECT*FROMemployeesWHEREofficeCode=5

Now, you can delete records associated withofficeCode5 from bothofficesandemployeestables:

DELETEoffices,employeesFROMofficesINNERJOINemployeesONemployees.officeCode=employees.officeCodeWHEREoffices.officeCode=5

If you use theSELECTstatements above to query office and employee data withofficeCode5 in theofficesandemployeestables again, you will not see any row returned.

MySQL DELETE JOIN with LEFT JOIN

You often useLEFT JOINclause in theSELECTstatement to find records that exist in the left table and does not have corresponding records in the right table. You can also use theLEFT JOINclause in theDELETEstatement 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 useDELETEstatement withLEFT JOINclause to delete records fromT1table that does not have corresponding records in theT2table:

DELETET1FROMT1LEFTJOINT2ONT1.key=T2.keyWHERET2.keyISNULL

Note that you only putT1table after theDELETEkeyword, not bothT1andT2tables like you did with theINNER JOINclause.

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 useDELETEstatement withLEFT JOINclause to clean up our customers master data. The following statement removes customers who do not have any order:

DELETEcustomersFROMcustomersLEFTJOINordersONcustomers.customerNumber=orders.customerNumberWHEREorderNumberISNULL;

Now, if we query customers who have not ordered any product by using the followingSELECTLEFT JOINquery:

SELECTc.customerNumber,c.customerName,orderNumberFROMcustomerscLEFTJOINordersoONc.customerNumber=o.customerNumberWHEREorderNumberISNULL;

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 withINNER JOIN andLEFT JOINto delete data from multiple tables.

原文链接：http://outofmemory.cn/mysql/mysql-delete-join