MySQL DELETE JOIN is a very important point while preparing for an interview. Suppose you are going for a database role. In that case, it is very necessary that you have a proper understanding of the DELETE JOIN statement and how to apply it in practical examples.
So in this tutorial, we will learn everything about the DELETE JOIN statement. We will start with the introduction and then see its types and syntax. We will take examples of each type to understand the topic thoroughly.
Introduction to DELETE JOIN Statement
The DELETE JOIN statement is used to delete rows from the table and the matching rows from another table. It is a combination of the DELETE and JOIN statements which is totally different from the DELETE CASCADE statement.
MySQL DELETE JOIN Examples
The DELETE JOIN is based on two types of joins- INNER JOIN and LEFT JOIN. We will see examples of both types below.
1. MySQL DELETE JOIN with INNER JOIN
The DELETE with INNER JOIN is used to delete all the rows from the first table and the matching rows from the second table on a certain condition.
Consider an example below-
DELETE t1, t2
FROM t1
INNER JOIN t2 ON t1.key = t2.key
WHERE condition;Code language: SQL (Structured Query Language) (sql)Here, we have two tables: t1 and t2. When we write both table names with the DELETE, rows from both tables get deleted.
If we skip the t1, then the DELETE will delete the rows from the t2 table only. On the other hand, if we skip the t2, the DELETE will delete the rows from the t1 table only.
The INNER JOIN will execute on the condition of t1.key=t2.key. Whereas the WHERE condition will determine which rows will be deleted.
MySQL DELETE JOIN with INNER JOIN Example
To demonstrate the example of DELETE INNER JOIN, we will create tables and insert data into them.
CREATE TABLE t1(
id INT PRIMARY KEY,
);
CREATE TABLE t2(
ref_id INT,
date NOW DEFAULT(NOW())
);
INSERT INTO t1(id)VALUES
(1),(2),(3),(4);
INSERT INTO t2(ref_id) VALUES
(1),(3),(4);Code language: SQL (Structured Query Language) (sql)Let’s display table data to check if the values are inserted correctly.
SELECT * FROM t1;
SELECT * FROM t2;Code language: SQL (Structured Query Language) (sql)
As you can see, table t2 contains some ids that are present in table t1.
Now, we want to delete the rows from both tables on the basis of ids. But, we will delete only one row of id 4 from both tables. If id 4 is present in both tables, the record will get deleted. Else, no record will be deleted.
DELETE t1,t2 FROM t1
INNER JOIN t2 ON t1.id=t2.ref_id
WHERE t2.ref_id=4;Code language: SQL (Structured Query Language) (sql)
As you can see in the above image, the rows of id 4 in both tables are deleted.
MySQL DELETE JOIN with LEFT JOIN
The LEFT JOIN is widely used with the SELECT statement to return all rows from the left table and the matching or non-matching records from the right table. Likewise, the DELETE LEFT JOIN is used to delete the records from the left table on a certain condition.
Note that even if the records from the left table don’t match the right table, we can delete the records from the left table.
Let’s see an example to understand the working of the DELETE LEFT JOIN statement.
In the example below, we will delete the records from the first (left) table, which doesn’t match the records in the second (right) table. To do that, we will use the WHERE condition and check if the column’s value is null.
But before that, let’s see the LEFT JOIN output, so that you will understand what will be deleted from the table.
SELECT * FROM t1
LEFT JOIN t2
ON t1.id=t2.ref_id;Code language: SQL (Structured Query Language) (sql)
Here, you can see that we have got the NULL values for id 2 because there is no record in the t2 table of ref_id 2. So, this record should be deleted if we use the DELETE LEFT JOIN.
MySQL DELETE JOIN with LEFT JOIN Example
DELETE t1 FROM t1
LEFT JOIN t2 ON t1.id=t2.ref_id
WHERE t2.ref_id IS NULL;Code language: SQL (Structured Query Language) (sql)Here, we are trying to delete the records from table 1 on the basis of t1.id=t2.ref_id. We have applied the condition that if the id of t1 is not present in table t2, then delete that record from the t1.
As you can see in the above image, the record of id 2 is deleted from table t1.
This way, we can use the LEFT JOIN with the DELETE statement to delete the records.
Conclusion
In this tutorial, we learned what the DELETE JOIN statement is and its use. We also went through the types and syntax of each. Then we learned the DELETE JOIN through practical examples. I hope you have easily understood the topic. We recommend you try as many examples as possible to avoid confusion in the future.