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.

SQL - Temp Table (Local & Global Temp Tables)

Temporary table is also known as temp table. This is another most usable feature of the SQL Server. Temp tables are used to store temporary result set which is defined within the execution scope of a single SQL statement such as SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW. 
Temp tables have the same feature as any normal data table. We can create indexes on the temp table but it depends on the requirements.

Whenever we create a temporary table, it goes to Temporary folder of tempdb database. Based on the behavior and scope of requirement, SQL server provides two types of temp tables as given below - 

1. Local Temp Table , 
2. Global Temp Table

Local Temp Tables – They are easily accessible for single user. This is the most usable in SQL server and works only in the current connection of the user. They automatically deleted from the session whenever user disconnects from instances. We can create them by starting with hash (#) sign as given below -

1. We can create them as we create permanent data tables in the database before use them and
2. We can create them by using into temp table name from the permanent table or views in the select statements. They get the same data structure from the select data columns which is very useful to avoid data conflict.

----- syntax to create temp table create table #TempEmp ( EmpId Int, EmpName Varchar(25), EmpAge int, EmpDept varchar(6) ) ----- how to insert data into temp table Insert into #TempEmp  values (1,'ABC',21,'D001'), (2,'DEF',22,'D011'), (3,'GHI',23,'D021'), (4,'JKL',24,'D031') ----- get result from the temp table select EmpId, EmpName, EmpAge, EmpDept From #TempEmp EmpId EmpName EmpAge EmpDept 1 ABC 21 D001 2 DEF 22 D011 3 GHI 23 D021 4 JKL 24 D031 ----- get filtered result into another temp table select EmpId,EmpName, EmpAge,EmpDept into #Empbelow22 From #TempEmp where EmpAge>22 ----- pull the data from select EmpId, EmpName, EmpAge, EmpDept from #Empbelow22 EmpId EmpName EmpAge EmpDept 1 ABC 21 D001 2 DEF 22 D011 ----- drop temp tables drop table #Empbelow22, #TempEmp

Global Temp Tables –They are easily accessible for multiple users. The scope of Global temporary table is the same for the entire user for a particular connection. They automatically deleted whenever all users sessions are disconnected. We can create them by starting with double hash (##) sign. We cannot set revoke and grant permissions to the global temp table because it’s always accessible to all users.

----- syntax to create Global temp table create table ##TempEmp ( EmpId Int, EmpName Varchar(25), EmpAge int, EmpDept varchar(6) ) ----- how to insert data into Global temp table Insert into ##TempEmp  values (1,'ABC',21,'D001'), (2,'DEF',22,'D011'), (3,'GHI',23,'D021'), (4,'JKL',24,'D031') ----- get result from the Global temp table select EmpId, EmpName, EmpAge, EmpDept From ##TempEmp EmpId EmpName EmpAge EmpDept 1 ABC 21 D001 2 DEF 22 D011 3 GHI 23 D021 4 JKL 24 D031

When can we use of temp tables –

There are lots of scenarios, where we can easily use of the temp table to increase the performances and solve the problems such as –

• In the stored procedure, wherever we require a large number of row manipulations. 
• They could be the replacement of the cursor in the query to store the result set data into a temp table, then we can manipulate the data from there. 
• They are the best option to do complex join operations.
• We can use of n-numbers of temp tables to do more complex data manipulation and call them in the entire single session.

Point to remember: To avoid additional overhead and performance issues, drop the temp tables after use of the temp table because Temporary table stored on tempdb of SQL Server which is a separate database.
To know more on the different kinds of the tables in SQL Servers at

1. SQL – Wide Tables
2. SQL - Table Variables
3. SQL - Temp Table (Local & Global Temp Tables)
4. SQL - Common Table Expression
5. SQL - Difference between Table Variable and Common Type Expression
6. SQL - Difference between Temp Table and CTE
7. SQL - Difference between Temp Table and Table Variable