DELETE JOIN statement is used to delete the rows or values from one table that are matched with another table rows or values.

DELETE target_tables|VIEW FROM table1 INNER JOIN|LEFT JOIN|RIGHT JOIN
table2 ON table1.column_name = table2.column_name WHERE condition; 

• target_table - Specifies the table1 or table2 or both or other table which have the same data as either table1 or table2.

Let us consider below table(s) as an example table(s) to frame the SQL query for getting the desired results.

Scenario – Delete one table rows that are matched with another table.

Requirement – Delete employees from employee_details table who are having department location as Location1. The query was as follows –

DELETE employee_deails
       FROM employee_details INNER JOIN department_details
       ON employee_details.dept_id = department_details.dept_id
       WHERE department_details.location = "Location1"; 

By executing above query, we can delete rows from employee_details who are having department location as Location1. The table after deletion was as follows -

Scenario – Delete rows from both tables.

Requirement – Delete department 2000 details from employee_details, department_details table. The query was as follows –

DELETE employee_details, department_details
     FROM employee_details INNER JOIN department_details
     ON employee_details.dept_id = department_details.dept_id
     WHERE  department_details.dept_id = 2000; 

By executing above query, we can delete rows of employees whose dept_id is 2000 from employee_details and department_details tables. The tables after deletion was as follows -