SQL - Delete using INNER JOIN in SQL

Delete using INNER JOIN in SQL

We always try to keep normalization in our database and maintain table relationship for each record as possible. To maintain normalization, we always put our records in more than two tables by making relationship between them which are highly tide up mostly on primary and foreign key. Sometimes, it is become very urgent to us to remove matching records from one table based on the other table records then these relationship come into the picture.

Note: We can only delete 1 table at a time by using delete command with joins.

To understand the delete using joins, we have two tables structure to store the above scenario such as:

USE tempdb; GO ------ create temp table to store employees information CREATE TABLE [dbo].[EmployeeMaster] ( [EmpId] [int] IDENTITY(101,1) NOT NULL, [EmpName] [varchar](50) NOT NULL, [EmpSalary] [int] NOT NULL, [EmpAge] [int] NOT NULL, [DeptId] [int] NULL ) ON [PRIMARY] ------ create another temp table to store department list CREATE TABLE [dbo].[Department] ( [DeptId] [int] IDENTITY(101,1) NOT NULL, [DepartmentName] [varchar](50) NOT NULL ) ON [PRIMARY] ---- both tables have the relation on DeptId

After creating above tables, we need to insert some demo data into the both tables  as shown below:

---- both tables have the relation on DeptId ---- Insert Values in the employees Master table Insert into EmployeeMaster(EmpName, EmpSalary, EmpAge, DeptId ) Values ('Amit Gupta',15000,32,101), ('Anil Sharma',15500,22,102), ('Bill Smith',16500,25,103), ('Chris Gray',17500,28,104), ('David King',15500,22,102), ('Eliza Roy',15500,25,101), ('Flash God',15500,23,102) ---- Records in EmployeeMaster table Select EmpId, EmpName, EmpSalary, EmpAge, DeptId from EmployeeMaster EmpId EmpName EmpSalary EmpAge DeptId 101 Amit Gupta 15000 32 101 102 Anil Sharma 15500 22 102 103 Bill Smith 16500 25 103 104 Chris Gray 17500 28 104 105 David King 15500 22 102 106 Eliza Roy 15500 25 101 107 Flash God 15500 23 102 ---- Insert Records in Department table Insert into Department Values('Account'),('Finance'),('IT'),('Admin') ---- Records in Department table Select DeptId, DepartmentName from Department DeptId DepartmentName 101 Account 102 Finance 103 IT 104 Admin

Now, we need to delete all employees from the employee master table who are in ‘Admin’ department. We can delete them by using sub query also but here we will remove them by using delete command with joins as shown below:

---- delete the all employees from EmployeeMaster who are in 'Admin' Department DELETE Emp FROM EmployeeMaster Emp INNER JOIN Department Dept ON Dept.DeptId =Emp.DeptId WHERE Dept.DepartmentName='Admin'

You can see above that we have  just added the name of the table or table alias name between ‘DELETE’ and ‘FROM’ from where you want to delete records because we have to specify the table to delete.

We can view the data again in the employee master table where records for admin department are not available as shown below:

---- Records in EmployeeMaster table after delete command Select EmpId, EmpName, EmpSalary, EmpAge, DeptId from EmployeeMaster EmpId EmpName EmpSalary EmpAge DeptId 101 Amit Gupta 15000 32 101 102 Anil Sharma 15500 22 102 103 Bill Smith 16500 25 103 105 David King 15500 22 102 106 Eliza Roy 15500 25 101 107 Flash God 15500 23 102

Query at a Glance: You can run the below script to verify the functionality of the article as shared:

USE tempdb; GO ------ create temp table to store employees information CREATE TABLE [dbo].[EmployeeMaster] ( [EmpId] [int] IDENTITY(101,1) NOT NULL, [EmpName] [varchar](50) NOT NULL, [EmpSalary] [int] NOT NULL, [EmpAge] [int] NOT NULL, [DeptId] [int] NULL ) ON [PRIMARY] ------ create another temp table to store department list CREATE TABLE [dbo].[Department] ( [DeptId] [int] IDENTITY(101,1) NOT NULL, [DepartmentName] [varchar](50) NOT NULL ) ON [PRIMARY] ---- both tables have the relation on DeptId ---- Insert Values in the employees Master table Insert into EmployeeMaster(EmpName, EmpSalary, EmpAge, DeptId ) Values ('Amit Gupta',15000,32,101), ('Anil Sharma',15500,22,102), ('Bill Smith',16500,25,103), ('Chris Gray',17500,28,104), ('David King',15500,22,102), ('Eliza Roy',15500,25,101), ('Flash God',15500,23,102) ---- Records in EmployeeMaster table Select EmpId, EmpName, EmpSalary, EmpAge, DeptId from EmployeeMaster ---- Insert Records in Department table Insert into Department Values('Account'),('Finance'),('IT'),('Admin') ---- Records in Department table Select DeptId, DepartmentName from Department ---- delete the all employees from EmployeeMaster who are in 'Admin' Department DELETE Emp FROM EmployeeMaster Emp INNER JOIN Department Dept ON Dept.DeptId =Emp.DeptId WHERE Dept.DepartmentName='Admin' ---- Records in EmployeeMaster table after delete command Select EmpId, EmpName, EmpSalary, EmpAge, DeptId from EmployeeMaster ---- drop temp tables DROP TABLE EmployeeMaster, Department;

Now, you can see that we can remove records from one table which are matching with another tables without using sub queries to get the matching conditions.

Watch demo at YouTube channel - 

Recommended: You must view as SQL - Update using Inner join in SQL also.

To learn more, please follow us — 🔊 http://www.sql-datatools.com To Learn more, please visit our YouTube channel at — 🔊 http://www.youtube.com/c/Sql-datatools To Learn more, please visit our Instagram account at — 🔊 https://www.instagram.com/asp.mukesh/ To Learn more, please visit our twitter account at — 🔊 https://twitter.com/macxima