SQL DELETE JOIN

The SQL DELETE JOIN statement allows you to delete rows from one table based on matching conditions in another related table. It is useful for managing linked data across multiple tables while ensuring database consistency.

• Deletes rows from only one table even when multiple tables are joined.
• Uses joins to apply conditions based on related table data.
• Supports INNER JOIN, LEFT JOIN, and USING to match rows.
• Allows precise deletion using the WHERE clause.

Example: First, we create a demo SQL database and tables, on which we use the DELETE JOIN command.

Query:

DELETE employees
FROM employees
JOIN departments
ON employees.dept_id = departments.dept_id
WHERE departments.dept_name = 'HR';

• Joins the employees table with the departments table using dept_id.
• Deletes employees whose department name is HR.

Syntax:

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

• 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.

Example of DELETE JOIN

In this example, we demonstrate the implementation of DELETE JOIN. Consider the following tables for the example below:

Query:

DELETE Orders
FROM Orders
JOIN Customers
ON Orders.customer_id = Customers.customer_id
WHERE Customers.status = 'inactive';

SELECT * FROM Orders;

Output:

• Joins the Orders table with Customers using the customer_id column.
• Deletes rows from Orders where the customer status is inactive.
• Displays the remaining records in the Orders table using SELECT *.