DELETE JOIN

DELETE query is a sub-part of data manipulation language used for removing the rows from tables. How to delete join in MySQL is a very popular question during the interviews. It is not an easy process to use the delete join statements in MySQL. In this section, we are going to describe how you can delete records from multiple tables with the use of INNER JOIN or LEFT JOIN in the DELETE query.

DELETE JOIN with INNER JOIN

The Inner Join query can be used with Delete query for removing rows from one table and the matching rows from the other table that fulfill the specified condition.

Syntax

The following are the syntax that can be used for deleting rows from more than one table using Inner Join.

DELETE target table   

FROM    table1    

INNER JOIN table2  

ON table1.joining_column= table2.joining_column  

WHERE   condition 

Here, the target is a table name from where we want to delete rows by matching the specified condition. Suppose you want to delete rows from table T1 and T2 where student_id = 2, then it can be written as the following statement:

 DELETE T1, T2    

FROM    T1    

INNER JOIN T2    

ON T1.student_id=T2.student.id    

WHERE   T1.student_id=2; 

In the above syntax, the target table (T1 and T2) is written between DELETE and FROM keywords. If we omit any table name from there, then the delete statement only removes rows from a single table. The expression written with ON keyword is the condition that matches the rows in tables where you are going to delete.

Example

Suppose we have two table students and contacts that contains the following data:

Table: students

Table: contacts

Execute the following query to understand the Delete Join with Inner Join. This statement deletes a row that has the same id in both tables.

DELETE students, contacts FROM students  

INNER JOIN contacts ON students.student_id=contacts.college_id   

WHERE students.student_id = 4; 

After successful execution, it will give the following message:

Now, run the following query to verify the rows deleted successfully.

mysql> SELECT * FROM students;  

mysql> SELECT * FROM contacts;

You can see that the rows where the student_id=4 is deleted.

DELETE JOIN with LEFT JOIN

We have already learned the LEFT JOIN clause with SELECT statement that returns all rows from the left(first) table and the matching or not matching rows from another table. Similarly, we can also use the LEFT JOIN clause with the DELETE keyword for deleting rows from the left(first) table that does not have matching rows from a right(second) table.

The following query explains it more clearly where DELETE statement use LEFT JOIN for deleting rows from Table1 that does not have matching rows in the Table2:

DELETE Table1 FROM Table1  

LEFT JOIN Table2 ON Table1.key = Table2.key   

WHERE Table2.key IS NULL; 

In the above query, notice that we will only use Table1 with the DELETE keyword, not both as did in the INNER JOIN statement.

Example

Let us create a table “contacts” and “customers” in a database that contains the following data:

Table: contacts

Table: customers

Execute the following statement that removes the customer who does not have a cellphone number:

DELETE customers FROM customers  

LEFT JOIN contacts ON customers.customer_id = contacts.contact_id   

WHERE cellphone IS NULL; 

After successful execution, it will give the following message:

Now, run the following query to verify the rows deleted successfully.

mysql> SELECT * FROM customers;  

You can see that the rows where the customer does not have the cellphone number are deleted.