Delete From With Join in SQL Server

In this SQL Server tutorial, we will learn and understand how to use the SQL Server DELETE statement with the JOIN clause on tables by the query.

Recently, I was working on an Employee Management System Project where it was required to use the DELETE statement with the JOIN clause in the SQL Server. So, here we will focus on using the DELETE statement with the JOIN clause in the SQL Server.

Here is a list of topics that we are covering in this section:

How to delete from with Join in SQL Server
How to delete from with Left Join SQL Server
How to delete from with Right Join SQL Server
How to delete from with Full Join SQL Server
How to delete from with Join in SQL Server Group By

Table of Contents

Delete From With Join in SQL Server

In SQL Server, we use the JOIN clause to join multiple data from multiple tables. And to delete the common rows or related rows from the tables, we use the DELETE JOIN statement.

SYNTAX:

DELETE TABLE
FROM TABLE_1
[INNER | LEFT | RIGHT | FULL ]JOIN TABLE_2
ON TABLE_1.COMMON_COLUMN=TABLE_2.COMMON_COLUMN
WHERE [ CONDITIONS];

Now, we will create two master tables called EMPLOYEE_DETAILS and INVENTORY_DETAILS and one transaction table called ITEM_REQUESTS. Here are the images of the tables:

delete from with join in Sql server — EMPLOYEE_DETAILS (MASTER_TABLE)

delete from with join in Sql server example — INVENTORY_DETAILS (MASTER TABLE)

delete from with join in Sql server image — ITEM_REQUEST table (TRANSACTION TABLE)

Here we will delete the inventory which has been given back to the ITEM_REQUEST table by using the DELETE statement with the JOIN statement:

EXAMPLE:

DELETE ITEM_REQUEST
FROM ITEM_REQUEST
INNER JOIN INVENTORY_DETAILS
ON ITEM_REQUEST.ITEM_ID=INVENTORY_DETAILS.ITEM_ID
WHERE INVENTORY_DETAILS.STOCK<10
AND ITEM_REQUEST.IS_RETURN=1;

SELECT * FROM ITEM_REQUEST;

With the help of the INNER JOIN clause, we have deleted only those inventory records which are returned in the ITEM_REQUEST table and using the JOIN clause for the matching records from the EMPLOYEE_DETAILS table.

Then we used the SELECT statement to retrieve the remaining records from the ITEM_REQUEST table.

Example of delete from with join in Sql server — Example of DELETE FROM with INNER JOIN in SQL Server

We hope that you have understood how to use the INNER JOIN clause with the DELETE statement on tables by the query.

Read: Create a Database in SQL Server 2019

Delete From With Left Join SQL Server

The SQL Server LEFT JOIN clause is used to return all records from the TABLE_1 (left table) and with matching records from the TABLE_2 ( right table). The right table will bring 0 resultsets if there are no match records.

EXAMPLE:

DELETE ITEM_REQUEST
FROM ITEM_REQUEST
LEFT JOIN INVENTORY_DETAILS
ON ITEM_REQUEST.ITEM_ID=INVENTORY_DETAILS.ITEM_ID
WHERE ITEM_REQUEST.QUANTITY>=2
AND ITEM_REQUEST.IS_RETURN!=1;

SELECT * FROM ITEM_REQUEST;

With the LEFT JOIN clause, we are deleting those records from the ITEM_REQUEST table whose quantity is greater than 2. And the item not returned while joining with the INVENTORY_DETAILS table.

Delete from with left join in Sql server example — Example of Delete From With Left Join in SQL Server

We hope that you have understood how to use the DELETE statement with the LEFT JOIN clause on tables by the query.

Read: How to drop table if exists in SQL Server

Delete From With Right Join SQL Server

The SQL Server RIGHT JOIN is used to return all records from the TABLE_2 (right table) and with matching records from the TABLE_1 (left table ). If there are no matching records from the left table, it will retrieve 0 resultsets.

EXAMPLE:

DELETE ITEM_REQUEST
FROM ITEM_REQUEST 
RIGHT JOIN EMPLOYEE_DETAILS
ON ITEM_REQUEST.EMP_ID=EMPLOYEE_DETAILS.EMP_ID
WHERE ITEM_REQUEST.RETURN_DATE>='2022-12-31'
AND IS_RETURN!=1;

SELECT * FROM ITEM_REQUEST;

With the help of the RIGHT JOIN clause, we are deleting those records whose return_date is greater than or equal to 2022-12-31 and which item is not returned from the ITEM_REQUEST table while joining with the EMPLOYEE_DETAILS table. Then we used the SELECT statement to retrieve the remaining records from the ITEM_REQUEST table for the resultset.

delete from with right join Sql server example — Example of Delete From With Right Join in SQL Server

We trust you now understand how to utilize the DELETE command on tables returned by the query with the RIGHT JOIN clause.

Read: Identity Column in SQL Server

Delete From With Full Join SQL Server

In SQL Server, the FULL OUTER JOIN clause returns all records when there is a matching record from TABLE_1 ( right table) and TABLE_2 (left table).

EXAMPLE:

 
DELETE ITEM_REQUEST
FROM ITEM_REQUEST
FULL JOIN EMPLOYEE_DETAILS
ON ITEM_REQUEST.EMP_ID=EMPLOYEE_DETAILS.EMP_ID
WHERE ITEM_REQUEST.IS_RETURN=1 
AND ITEM_REQUEST.QUANTITY<=2;

SELECT * FROM ITEM_REQUEST;

In the ITEM_REQUEST table, we are deleting inventory that is returned and has a quantity less than 2 with the FULL JOIN clause.

And to get the result set, we have joined the EMPLOYEE_DETAILS table. Once the DELETE JOIN statement has been executed, we used the SELECT statement to retrieve the remaining record from the ITEM_REQUEST table.

delete from with full join Sql server example — Example of Delete From With Full Join SQL Server

We hope that you have understood how to use the SQL Server FULL JOIN clause with the DELETE statement on tables by the query.

Read: Arithmetic operators in SQL Server

Delete From With Join in SQL Server Group By

In SQL Server, the SELF JOIN clause is used to join a table to itself.

The SQL Server GROUP BY clause is used in collaboration with the SELECT statement to arrange identical data into groups. And it is often used with aggregate functions like SUM, MAX, AVG, MIN, and COUNT functions to group the result-set by one or more columns.

EXAMPLE:


DELETE I
FROM ITEM_REQUEST I
JOIN (
SELECT REQUEST_ID
FROM ITEM_REQUEST
WHERE IS_RETURN=1
GROUP BY REQUEST_ID
HAVING MIN(RETURN_DATE)>'2021-01-01'
)B ON I.REQUEST_ID=B.REQUEST_ID;

SELECT * FROM ITEM_REQUEST;

With the help of the SELF JOIN clause, we have deleted the records from the ITEM_REQUEST table where the IS_RETURN column value is 1. Then we used the SELECT statement to retrieve the remaining records from the ITEM_REQUEST table.

After the execution of the SELF JOIN clause with the DELETE statement, we used the SELECT statement to retrieve all records from the ITEM_REQUEST table.

delete from with join in Sql server group by — Example of Delete From With Join in SQL Server Group By

If we use the SELECT statement on the ITEM_REQUEST table, it will fetch out the records based on the WHERE and GROUP BY clauses for the resultset. And it is part of the DELETE statement records which are shown below:

SELECT REQUEST_ID
FROM ITEM_REQUEST
WHERE IS_RETURN=1
GROUP BY REQUEST_ID
HAVING MIN(RETURN_DATE)>'2021-01-01';

delete from with join in Sql server group by example — Example of SELECT statement to fetch records from the ITEM_REQUEST table by using the GROUP BY clause.

We hope that you have understood how to use the DELETE statement with the JOIN and GROUP BY clauses on the tables by the query.

Also, take a look at some more SQL Server tutorials.

We now know about the post “Delete From With Join in SQL Server “ after reading this tutorial. To assist you in comprehending the idea, we explored the following subtopics in detail.

How to delete from with Join in SQL Server
How to delete from with Left Join SQL Server
How to delete from with Right Join SQL Server
How to delete from with Full Join SQL Server
How to delete from with Join in SQL Server Group By

Bijay

I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.

Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.