Wednesday, October 21, 2015

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.
Recommended: You must view as SQL - Update using Inner join in SQL also.

No comments:

Post a Comment

Popular Posts

Get Sponsored by Big Brands