How to Delete a Table using Join?

Question

If we delete the base table whether the synonym will exists or not? If we use that synonym what type of error we will get?

Answer 1

Left Join fetches all rows of table1(left table) with matching rows of table2(right table). So we can delete using Left Join.
Example:
Delete employee_salary FROM employee_salary LEFT JOIN employee ON employee.id=employee_salary.employee_id
WHERE employee.first_name='Admin';

Answer 2

We often use INNER JOIN or OTHER JOIN to select the data from one table and the corresponding data from another table. MYSQL allows you to use INNER JOIN clause with DELETE statement so that you can easily delete records from one table and also the corresponding records from another table.

---

DELETE zpost ,zcomment FROM zpost INNER JOIN zcomment ON zpost.zpostid = zcomment.zpostid WHERE condition

---

NOTE : IF you omit zpost table, it would delete records from zcomment. AND if you omit zcomment then it would delete records from zpost.

---

MySQL DELETE JOIN with LEFT JOIN

You often use LEFT JOIN clause in the SELECT statement to find records that exist in the left table and does not have corresponding records in the right table. You can also use the LEFT JOIN clause in the DELETE statement to delete record in a table (left table) that does not have corresponding record in another table (right table).