SQL DELETE JOIN

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

---