SQL DELETE JOIN

The SQL DELETE JOIN statement allows you to delete rows from one table based on a condition in another table. This is particularly useful for maintaining data consistency across related tables.

By combining the DELETE statement with a JOIN, you can specify which rows to delete in one table by checking conditions against related rows in another table.

Example

This example demonstrates deleting orders from the Orders table where the customer is not listed in the Customers table.

-- Delete orders for customers not in the Customers table
DELETE Orders
FROM Orders
LEFT JOIN Customers
ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.CustomerID IS NULL;

-- View the Orders table after deletion
SELECT * FROM Orders;
            

Explanation

- The LEFT JOIN identifies orders with no matching customer in the Customers table.
- The WHERE Customers.CustomerID IS NULL condition filters out these orders for deletion.
- The Orders table is updated to remove entries for customers not listed in the Customers table.