SQL DELETE JOIN

The SQL DELETE JOIN statement is a powerful feature that allows us to delete rows from one table based on conditions involving another table. This is particularly useful when managing relationships between tables in a database. For example, we may want to delete rows in a "Library Books" table where certain "Student IDs" exist in a related "Students" table.

SQL DELETE JOIN

The DELETE JOIN operation in SQL delete rows from a table while considering conditions that involve another table. By combining DELETE with JOIN, we can efficiently manage related data across tables. This method allows us to join two or more tables and delete rows from the primary table that meet specific criteria, ensuring that the integrity of related data is maintained while removing unnecessary or outdated information.

While we can join multiple tables in the FROM clause, we can only specify one table in the DELETE clause. This means that although multiple tables can be used to define conditions for deletion, the rows can only be deleted from a single, explicitly stated table.

Key Features of DELETE JOIN

• Deletes rows from one primary table based on conditions set by joining additional tables.
  
• Only one table is listed explicitly in the DELETE clause.
  
• Ensures efficient data management and integrity across relational tables.
  
• Uses INNER JOIN, LEFT JOIN, or USING clauses to match data between tables.
  
• Can target specific rows using the WHERE clause.

Syntax:

DELETE table1
FROM table1 JOIN table2
ON table1.attribute_name = table2.attribute_name
WHERE condition

Key Terms:

• table1: The primary table from which rows will be deleted.
  
• table2: The table used for comparison or condition.
  
• ON: Specifies the condition for the JOIN.
  
• WHERE: Optional; filters which rows to delete.

Demo SQL Database

For this DELETE JOIN tutorial, we will use two example tables: students and library_books. These tables contain related data, where each student has an associated library book entry. This example demonstrates how to delete records from the library_books table based on matching student IDs from the students table.

1. Student Table

2. Library Books Table

Step-by-Step Example: Deleting Rows with DELETE JOIN

This section demonstrates how to use the DELETE JOINsyntax to remove rows from one table based on conditions involving another table. This example will walk you through the process of setting up the necessary tables, inserting data, and executing the DELETE JOIN query to delete specific rows efficiently.

Step 1: Create Tables

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
)

Step 2: Insert Sample Data

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)

Step 3: Delete a Row Using DELETE JOIN

Suppose we want to delete a row from the library_books table where the lib_id matches student_id 1001 in the students table for a specific student ID

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

Explanation

• Here, deletion occurs only in the library_books table.
• The join with the students table helps identify exactly which rows to remove.

Important Points about DELETE JOIN

• DELETE JOIN allows to delete rows from a table based on condition involving another table.
  
• We cannot delete from multiple tables in a single DELETE JOIN query.
  
• We can only specify one table in the DELETE clause, even if multiple tables are joined in the FROM clause.
  
• If a record is deleted from a table, related records in other table will be deleted too

Conclusion

The SQL DELETE JOIN operation is a strong way to manage and clean up data across multiple tables efficiently. By using the proper syntax and understanding how JOIN works, we can maintain a well-organized and optimized database. Always test our DELETE queries and ensure data integrity before applying them to production systems.