SQL DELETE JOIN

DELETE JOIN in SQL lets you delete rows of a table, based on conditions involving another table. We can use the DELETE statement with the JOIN operation to perform DELETE JOIN.

We use JOIN to combine data from multiple tables., to delete the same rows or related rows from the table at that time we use delete join.

In this article let us see how to delete multiple data using DELETE using JOIN by using MSSQL as a server.

Syntax

DELETE table1

FROM table1 JOIN table2

ON table1.attribute_name = table2.attribute_name

WHERE condition

Demo SQL Database

For this DELETE JOIN tutorial, we will use the following two tables in examples:

Table 1- Student

student_id	student_name	student_branch
1001	PRADEEP	E.C.E
1002	KIRAN	E.C.E
1003	PRANAV	E.C.E
2001	PADMA	C.S.E
2002	SRUTHI	C.S.E
2003	HARSITHA	C.S.E
3001	SAI	I.T
3002	HARSH	I.T
3003	HARSHINI	I.T

Table 2- Library books

lib_id	book_taken
1001	2
1002	3
1003	4
2001	2
3001	3

To create these tables on your system, write the following queries

MSSQL

CREATE DATABASE GeeksforGeeks;
USE GeeksforGeeks
CREATE TABLE student (
student_id VARCHAR(8),
student_name VARCHAR(20),
student_branch VARCHAR(20)
)

CREATE TABLE library_books(
lib_id VARCHAR(20),
book_taken INT
)

INSERT INTO students
VALUES( '1001','PRADEEP','E.C.E'),
( '1002','KIRAN','E.C.E'),
( '1003','PRANAV','E.C.E'),
( '2001','PADMA','C.S.E'),
( '2002','SRUTHI','C.S.E'),
( '2003','HARSITHA','C.S.E'),
( '3001','SAI','I.T'),
( '3002','HARSH','I.T'),
( '3003','HARSHINI','I.T')

INSERT INTO library_books
VALUES( '1001',2),
( '1002',3),
( '1003',4),
( '2001',2),
( '3001',3)

SQL DELETE JOIN Example

Query to delete library entry for id 1001 using JOIN

Query:

DELETE library_books
FROM  library_books JOIN students ON
students.student_id =library_books.lib_id
WHERE lib_id= 1001 
SELECT * FROM library_books

Output:

Key Takeaways about DELETE JOIN

• DELETE JOIN allows to delete rows from a table based on condition involving another table.
• We can use DELETE with JOIN to delete multiple rows from two or more tables.
• Using WHERE clause with JOIN allows to specify condition for deleting rows.
• If a record is deleted from a table, related records in other table will be deleted too