Delete from tables involved in a SQL Join

This question is not asked that often in an interview, but you may have to write a query to delete from tables that are involved in a SQL join in your day to day job routine as a SQL Developer.


Let me explain you the question first. Consider the Employees and Departments tables below.
I want you to write a query using joins which will give me the list of all HR Department employees. The output should be as shown below.



To do this we will write a query using joins as shown below.
Select E.Name as [Employee Name],D.Name as [Department Name]
from Employees E
Inner Join Departments D
On E.DeptId = D.Id
Where D.Name = 'HR'

Now I want you to write a query, that deletes all the HR department employees. To do this we have to join Employees and Departments tables and usually we will be tempted to write the query as shown below, but this query will not execute and will give a syntax error.

Incorrect SQL Delete Query to delete from multiple tables invloved in a SQL join
Delete From Employees E
Inner Join Departments D
On E.DeptId = D.Id
Where D.Name = 'HR'

The query below shows the correct syntax for issuing a SQL delete that involves a SQL join.
Delete E From (Employees E
Inner Join Departments D
On E.DeptId = D.Id
and D.Name = 'HR')

The following syntax is also valid and has the same effect.
Delete Employees From
(
Employees join Departments
on Employees.DeptId = Departments.Id
)
where Departments.Name = 'HR'