Summary -

In this topic, we described about the DELETE JOIN with detailed example.

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

Syntax -

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.

Example –

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

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

department_details -

dept_id dept_name dept_location
1000 Office Location1
2000 Sales Location2

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 -

emp_id emp_name designation manager_id date_of_hire salary dept_id
002 Employee2 Director 2019-07-11 40000.00 2000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

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 -

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000

department_details -

dept_id dept_name dept_location
1000 Office Location1