SQL advanced -> Delete records from one table using join with another table

Ok, the mission is to delete all employee records if in the title of their department can be found word "sales". It can happen in the real life. Imagine that newlly elected CEO decides to close all sales operations.

MS Access syntax will be:

1. The well known way, that uses sub-query:
DELETE *
FROM Employees
WHERE DeptNo IN
  (SELECT DeptNo
  FROM Departments
  WHERE LCase(DeptName) LIKE '*sales*')


2. The more efficient way is to use "Delete Join":
DELETE a.*
FROM Employees AS a INNER JOIN Departments AS b
ON a.DeptNo = b.DeptNo
WHERE LCase(b.DeptName) LIKE '*sales*'


3. Same query ("Delete Join") on SQLServer 2005:
DELETE a
FROM Employees AS a INNER JOIN Departments AS b
ON a.DeptNo = b.DeptNo
WHERE LOWER(b.DeptName) LIKE '%sales%'

4. Abstruct query ("Delete Join") on SQLServer 2005/2008:
Suppose that we have two tables. Fitting numbers in id column are marked by green color.
Table1 Table2
idItemName
1item1
2item2
4item4
5item5
id
2
3
5
9
Following delete statement will remove records with id 2 and 5 from Table1:
DELETE a
FROM Table1 AS a INNER JOIN Table2 AS b
ON a.id = b.id


sqlexamples.info