Open In App

MySQL DELETE JOIN

Last Updated : 01 Jan, 2024
Improve
Improve
Like Article
Like
Save
Share
Report

MySQL is an open-source, user-friendly, powerful, and popular choice, relational database management system. When maintaining and modifying data, tables usually interact in a complex way. MySQL’s DELETE JOIN function is one of its most powerful functions. MySQL DELETE JOIN is explored in detail in this article, which also offers examples to help visualize its capabilities with Right Join, Left Join, Inner Join, and Subqueries.

With the help of the MySQL DELETE JOIN function, we can delete row entries from one table in response to a condition related to another table. This is very useful when we need to delete rows based on specific conditions from data that exists across many linked tables.

Syntax:

DELETE target_table FROM table1

JOIN table2 ON table1.joining_column = table2.joining_column

WHERE some_condition;

Examples of MySQL DELETE JOIN

Let’s have two tables, the first is employees and the other is salaries, those contain the following data:

Example 1

Table employees:

1_tbl_employees

tbl_employees

Table salaries:

2_tbl_salaries

tbl_salaries

In this example:

  • The employees table has columns employee_id, employee_name, and department.
  • The salaries table has columns employee_id and salary.
  • The employee_id column is used as a common column to link the two tables.

Now, if we execute the DELETE JOIN query from the previous response:

DELETE emp FROM employees emp
JOIN salaries sal ON emp.employee_id = sal.employee_id
WHERE sal.salary < 50000;

It will delete the rows corresponding to the records from the employees table, as the salary is below 50,000.

After the deletion, the employees table will look like this –

Output:

output1_tbl_employees

output_tbl_employees

Let’s have some tables, students, courses , enrollmentsthat, grades , that contains the following data:

Table students:

4_tbl_students

tbl_students

Table courses:

5_tbl_courses

tbl_courses

Table enrollments:

6_tbl_enrollments

tbl_enrollments

Table grades:

7_tbl_grades

tbl_grades

Now, if we execute the DELETE JOIN query from the Example 2 tables:

DELETE st FROM students st
RIGHT JOIN enrollments en ON st.student_id = en.student_id
LEFT JOIN grades gd ON en.enrollment_id = gd.enrollment_id
WHERE gd.grade_id IS NULL;

It will delete those rows corresponding to students with neither enrollment nor grades.

After the deletion, the employees table will look like this –

Output:

output4_tbl_students

output_tbl_students

DELETE Statement with INNER JOIN

Now, if we again execute the DELETE INNER JOIN query from the Example 2 tables:

DELETE FROM students
WHERE NOT EXISTS (
SELECT 1
FROM enrollments en
INNER JOIN grades gd ON en.enrollment_id = gd.enrollment_id
WHERE students.student_id = en.student_id
);

It will delete those rows from the students table with no associated enrollments and grades.

After the deletion, the employees table will look like this –

Output:

output3_tbl_students

output_tbl_students

Using Subquery With INNER JOIN

Now, if we again execute the DELETE JOIN in subquery from the Example 2 tables:

DELETE FROM students
WHERE student_id IN (
SELECT student_id
FROM (
SELECT st.student_id
FROM students st
LEFT JOIN enrollments en ON st.student_id = en.student_id
LEFT JOIN grades gd ON en.enrollment_id = gd.enrollment_id
WHERE en.enrollment_id IS NULL AND gd.grade_id IS NULL
) AS subquery
);

It will delete those rows from students table who do not have any associated enrollments and grades.

After the deletion, the employees table will look like –

Output:

output5_tbl_students

output_tbl_students

Conclusion

MySQL DELETE JOIN is a strong feature for maintaining data security and cleanliness in complex database structures. Whether using Left Join for inclusive deletions or Inner Join for exact deletions or Right Join for accurate deletions, understanding this operation is important for effective database management. Always exercise caution when performing DELETE operations, especially in complex scenarios, to avoid accidental data loss. Testing on a small dataset or a backup copy of the database is suggested.


Previous Article
Next Article

Similar Reads

Difference Between Anti-Join and Semi-Join
In the context of SQL, Anti-join, and semi-join are two essential operations in relational databases used for querying and manipulating data. These operations focus on comparing data from two related tables, but they serve distinct purposes. In this article let us discuss these two operations in detail along with some examples. What is SQL Join?Joi
4 min read
MySQL RIGHT JOIN
In databases, data is stored in multiple tables and it is often necessary sometimes to combine two or more tables to fetch the required data. In MySQL, joins enable the merging of multiple tables based on the common columns. In this article, we are going to explore MySQL RIGHT JOINS which is a type of outer join in MySQL. Introduction to MySQL RIGH
7 min read
MySQL UPDATE JOIN
A widely used open-source relational database management system that allows you to efficiently store, organize, and retrieve data. Developed by Oracle, My SQL is widely used for building and managing databases that handle interactive websites and applications. We'll discuss the syntax, and demonstrate how this dynamic duo can efficiently modify dat
6 min read
MySQL CROSS JOIN
MySQL is an open-source relational database management system that uses Structured Query Language (SQL) to manipulate databases. It stores data in a table format. It provides various statements to perform Create, Read, Update, and Delete operations on a database table. Among these operations, MySQL also provides the CROSS JOIN statement to combine
3 min read
MySQL Inner Join
Have you encountered a query requiring you to somehow join or get the data spread across two or more tables? Are you wondering how to achieve the same by writing a MySQL query? Do you want to learn about MySQL INNER JOIN? If your answer to any of these questions is "YES", you have arrived at the correct place. Follow along with the below article an
7 min read
MySQL SELF JOIN
Joins are very important for effective data retrieval and analysis. The 'JOIN' clause is used to combine data from two or more tables using the common column between them. In MySql, there are many types of joins like inner join, outer join, left join, right join, full join, and self join. In this article, we will discuss the concept of MySQL SELF J
4 min read
INNER JOIN ON vs WHERE clause in MySQL
Have you ever wondered about the optimal use of INNER JOIN ON versus the WHERE clause in MySQL queries? In this article, we'll delve into the distinctions between these two approaches, elucidating their respective purposes, syntax, and best practices. By the end, you'll have a solid grasp of how to leverage these tools to enhance the efficiency and
4 min read
MySQL LEFT JOIN
In databases, data is stored in multiple tables and it is often necessary sometimes to combine two or more tables to fetch the required data. In MySQL, Joins enable the merging of multiple tables based on the common columns.  In this article, we are going to explore MySQL LEFT JOIN Keyword which is a type of Outer Join in MySQL. We will cover every
7 min read
How to Use Full Outer Join in MySQL
The FULL OUTER JOIN or FULL JOIN is a useful technique in MySQL that allows us to fetch all rows from both tables when there is a match in the records. In this article, we will learn about the FULL OUTER JOIN through practical examples and MySQL queries. MySQL FULL OUTER JOINThe FULL OUTER JOIN in MySQL returns all rows of both tables involved in t
4 min read
When should we use CROSS APPLY over INNER JOIN in MySQL
CROSS APPLY and INNER JOIN are used in MySQL to combine data from multiple tables. When dealing with row-wise operations or dynamic subqueries, CROSS APPLY is more efficient than INNER JOIN. CROSS APPLY allows for more detailed control on individual rows, while INNER JOIN operates on sets of data. It becomes very useful when we need to perform row-
4 min read