11. DELETE JOIN

Summary: in this tutorial, we will show you how to delete data from multiple tables by using MySQL DELETE JOIN statement.

In the previous tutorial, you learned how to delete rows of multiple tables by using:

• A single DELETE statement on multiple tables.
• A single DELETE statement on multiple related tables which the child table has an ON DELETE CASCADE referential action for the foreign key.

This tutorial introduces to you a more flexible way to delete data from multiple tables using INNER JOIN or LEFT JOIN clause with the DELETE statement.

MySQL DELETE JOIN with LEFT JOIN

• The DELETE LEFT JOIN statement allows you to delete rows from the left table (T1) when there is no matching row in the right table (T2).
• This is useful for removing rows that do not have corresponding entries in another table.

DELETE T1 
FROM T1
LEFT JOIN T2 ON T1.key = T2.key 
WHERE T2.key IS NULL;

Explanation

1. DELETE T1: Deletes rows from the left table (T1).
2. LEFT JOIN: Joins the tables, ensuring all rows from T1 are included, even if there is no match in T2.
3. ON T1.key = T2.key: Specifies the matching condition between the two tables.
4. WHERE T2.key IS NULL: Deletes rows in T1 that do not have a matching row in T2.

Example of DELETE with LEFT JOIN

Imagine you have tables customers and orders. Each customer may or may not have placed an order:

-- Example DELETE with LEFT JOIN
DELETE customers 
FROM customers
LEFT JOIN orders ON customers.customerNumber = orders.customerNumber 
WHERE orderNumber IS NULL;

Explanation:

• This query deletes customers who have no orders in the orders table.
• The LEFT JOIN ensures all customers are checked, and the WHERE orderNumber IS NULL condition filters out those without orders.

Verification Query: To verify the deletion:

SELECT 
    c.customerNumber, 
    c.customerName, 
    orderNumber
FROM
    customers c
LEFT JOIN orders o ON c.customerNumber = o.customerNumber
WHERE
    orderNumber IS NULL;

• This query checks if there are any customers left who do not have orders. If the result is empty, the deletion was successful.

Key Points to Remember

1. DELETE with INNER JOIN:
• Deletes rows from multiple tables based on matching conditions.
• Specify the tables from which rows should be deleted using DELETE T1, T2.
• Only deletes rows where there is a match between the joined tables.
2. DELETE with LEFT JOIN:
• Deletes rows from the left table (T1) that do not have a corresponding row in the right table (T2).
• Useful for cleaning up orphaned rows that have no matching related data.
3. WHERE Clause:
• Use the WHERE clause to refine which rows are targeted for deletion.

Summary

• The DELETE JOIN statement allows you to delete rows based on relationships between tables.
• Use INNER JOIN when you want to delete rows where matching data exists in both tables.
• Use LEFT JOIN when you want to delete rows in the left table that do not have a match in the right table.

These DELETE JOIN statements are powerful tools for maintaining data consistency and cleaning up unwanted or obsolete data in relational databases.