How to use Subquery With INNER JOIN In MySQL
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 the students table who do not have any associated enrollments and grades.
After the deletion, the employees table will look like this –
Output:
MySQL DELETE JOIN
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.