MySQL DELETE WITH JOIN

In this article, we will see how to delete the rows from MySQL tables associated with joins particularly,

• Syntax
• Delete one table with join in Example1
• Delete multiple tables with join in Example2
• Delete with join for specific conditions in Example3

Generally, DELETE with JOINS is used when we want to delete rows from one or more tables related to each other and through a specified column and a condition. Let us get started by making the sample data to be used across.

We will be creating two tables and inserting some rows into them.

# TABLE student_college_details
CREATE TABLE student_college_details (
student_id INT,
student_college_name VARCHAR(50)
);
INSERT INTO student_college_details(student_id,student_college_name) 
VALUES(1,"XVR Academy"),
(2,"XVR Academy"),
(3,"XVR Academy"),
(4,"Learner's Intitute"),
(5,"Learner's Intitute"),
(6,"XVR Academy"),
(14,"Learner's Intitute"),
(15,"Learner's Intitute"),
(16,"XVR Academy"),
(17,"Learner's Intitute");
# TABLE student_personal_details
CREATE TABLE student_personal_details (
student_id INT,
student_name VARCHAR(255),
student_ssn_no BIGINT
);
INSERT INTO student_personal_details(student_id,student_name,student_ssn_no) 
VALUES(1,'Daniel',1147483782),
(2,'Sandy',1147483783),
(3,'Veronica',1147483784),
(4,'Jennifer',1147483785),
(5,'Austin',1147483786),
(6,'Veronica',1147483787),
(7,'George',11474837988),
(23,'Veronica',1147483789),
(24,'Havard',1147483790),
(25,'Mounica',1147483791),
(26,'Will',1147483792),
(27,"Ryma",1147483793);

Let us have a view of the snapshot of both the tables.

SELECT * FROM student_college_details;

Output:-

SELECT * FROM student_personal_details;

Output:-

As we can see in image_1 and image_2, there are some rows with common student_id and some different ones.

Syntax:-

DELETE Table1, Table2, Table3..... FROM Table1
JOIN Table2 ON Table1.column_name1 = Table2.column_name1
JOIN Table3 ON Table2.column_name2 = Table3.column_name2
....
WHERE condition;

Example1:-

Delete the rows from the table student_college_details that have the same student_id as in student_personal_details.

Observe the below query for the solution.

DELETE sc FROM student_college_details sc
        JOIN
    student_personal_details sp ON sc.student_id = sp.student_id;

Action Message Output Response:-

6 row(s) affected.

Explanation:-

It shows 6 rows are affected, which means 6 rows got deleted. Those should be the six rows with common student_id from value 1 to 6. The query has applied an inner join on the tables student_college_details and student_personal_details. In the query, we are deleting only the records from student_college_details as it says delete sc (an alias for student_college_details).

Let us now verify if the rows with common student_id were deleted from table student_college_details by executing:

SELECT * FROM student_college_details;

Output:-

Output in image_3 shows that only the rows that do not have common student_id retained, and the rest got deleted.

Similarly, if we want to delete only the rows from student_personal_details with the same student_id as in student_college_details, we can execute the below query.

DELETE sp FROM student_personal_details sp
        JOIN
    student_college_details sc ON sp.student_id = sc.student_id;

Action Message Output Response:-

6 row(s) affected.

We can verify the deletion by looking into the snapshot of student_personal_details.

Output:-

Output in image_4 shows that only the rows that do not have common student_id got retained in the table student_personal_details rest got deleted.

Example 2:-

Delete the rows from both the tables student_college_details and student_personal_details, which have the same student_id.

Observe the below query for the solution.

DELETE sp , sc FROM student_personal_details sp
        JOIN
    student_college_details sc ON sp.student_id = sc.student_id;

Action Message Output Response:-

12 row(s) affected.

Explanation:-

The message shows that 12 rows got deleted from both the tables collectively. The query is making a join on both tables. The delete clause before sp (student_personal_details) and sc (alias for student_college_details) causes the deletion from both the tables. We can verify if the deletion was successful or not by executing:

SELECT 
    sp.student_id , sp.student_name ,sc.student_college_name
FROM
    student_college_details sc
        JOIN
    student_personal_details sp ON sc.student_id = sp.student_id;

Output:-

None of the rows are selected, which means that no rows are present in both the tables, which have common student_id values.

Example 3:-

We can also delete from multiple tables when specific conditions are true.

Delete the rows from the table student_college_details for all the students with the name Veronica.

Observe the below query for the solution.

DELETE sc FROM student_college_details sc
JOIN student_personal_details sp ON sc.student_id = sp.student_id
WHERE sp.student_name ="Veronica";

Action Message Output Response:-

2 row(s) affected.

Explanation:-

The message shows that 2 rows got deleted. The query deletes from the student_college_details table after making an INNER JOIN with the table student_personal_details. The JOIN is made ON student_id, and with a specific condition in WHERE clause – student_name is’Veronica’.

Let us verify if the delete was successful by executing:

SELECT 
    sc.student_id , sc.student_college_name 
FROM
    student_college_details sc
        JOIN
    student_personal_details sp ON sc.student_id = sp.student_id
WHERE
    sp.student_name = 'Veronica';

Action Message Output Response:-

0 row(s) returned.

Output:-

The output in image_6 shows that no rows got selected from the table student_college_data where student_name corresponding to particular student_id is Veronica from table student_personal_details.

We hope this article helped you to have an understanding of deleting with joins. Good Luck!!!