MySQL - Delete Join

MySQL: Deleting Records with JOINs

When to Use DELETE with JOIN

A standard DELETE statement operates on a single table. However, you often need to delete rows from one table based on a condition in another, related table. This is where the DELETE...JOIN syntax becomes incredibly powerful. For example, you might want to delete all customers who have not placed an order in the last year.

This operation allows you to perform conditional deletions across multiple tables in a single, atomic statement, which is more efficient and safer than fetching IDs and then running a separate DELETE command.

Syntax for DELETE with JOIN

The basic syntax involves specifying which table(s) to delete from, followed by the FROM and JOIN clauses that link the tables.

DELETE table1_alias, table2_alias
FROM table1 AS table1_alias
[INNER | LEFT | RIGHT] JOIN table2 AS table2_alias
ON table1_alias.join_column = table2_alias.join_column
WHERE condition;

Crucially, you must list the tables (or their aliases) from which you want to delete records right after the DELETE keyword. If you only want to delete from table1, you would write DELETE table1_alias FROM ....

Example Setup: Customers and Orders

Let’s set up two tables: CUSTOMERS and ORDERS.

CREATE TABLE CUSTOMERS(
    ID INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(100) NOT NULL,
    STATUS VARCHAR(20) DEFAULT 'active'
);

CREATE TABLE ORDERS(
    OID INT PRIMARY KEY AUTO_INCREMENT,
    ORDER_DATE DATE NOT NULL,
    CUSTOMER_ID INT,
    AMOUNT DECIMAL(10, 2),
    FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMERS(ID) ON DELETE SET NULL
);

INSERT INTO CUSTOMERS (NAME, STATUS) VALUES ('Ramesh', 'active'), ('Khilan', 'inactive'), ('Kaushik', 'active'), ('Muffy', 'active');

INSERT INTO ORDERS (ORDER_DATE, CUSTOMER_ID, AMOUNT) VALUES
('2023-10-08', 3, 3000.00),
('2022-01-15', 2, 1500.00),
('2023-11-20', 3, 1560.00),
('2021-05-20', 4, 2060.00);

In this scenario, customer ‘Ramesh’ (ID 1) has no orders, and customer ‘Khilan’ (ID 2) is marked as inactive.

DELETE with INNER JOIN

Let’s delete both the customer record and their associated orders for any customer marked as ‘inactive’. We specify both aliases (c, o) in the DELETE clause to remove rows from both tables.

DELETE c, o
FROM CUSTOMERS AS c
INNER JOIN ORDERS AS o ON c.ID = o.CUSTOMER_ID
WHERE c.STATUS = 'inactive';

This query will delete the ‘Khilan’ record from CUSTOMERS and their corresponding order from the ORDERS table.

DELETE with LEFT JOIN

A LEFT JOIN is perfect for finding records in one table that have no corresponding record in another. Let’s delete customers who have never placed an order.

DELETE c
FROM CUSTOMERS AS c
LEFT JOIN ORDERS AS o ON c.ID = o.CUSTOMER_ID
WHERE o.OID IS NULL;

In this case, the LEFT JOIN will find all customers. For those without orders, all columns from the ORDERS table (aliased as o) will be NULL. The WHERE o.OID IS NULL condition identifies these customers. The query will delete ‘Ramesh’ (ID 1) from the CUSTOMERS table.

Best Practice: Verify with SELECT First

This is the most important rule. Before running a destructive DELETE...JOIN query, always run it as a SELECT * first to see exactly which rows will be affected. This can prevent catastrophic mistakes.

To verify the previous LEFT JOIN example, you would run:

SELECT c.*, o.*
FROM CUSTOMERS AS c
LEFT JOIN ORDERS AS o ON c.ID = o.CUSTOMER_ID
WHERE o.OID IS NULL;

Review the output carefully. If it shows the rows you intend to delete, you can confidently replace SELECT c.*, o.* with DELETE c.

Alternative: The USING Clause

MySQL also supports a DELETE...USING syntax, which some developers find more readable. It separates the tables being deleted from the tables used for the join logic.

Example: Deleting inactive customers with USING

-- Note: The tables to delete from are listed before USING
DELETE FROM CUSTOMERS
USING CUSTOMERS
INNER JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
WHERE CUSTOMERS.STATUS = 'inactive';

This query will delete only from the CUSTOMERS table. To delete from both, you would list them: DELETE FROM CUSTOMERS, ORDERS USING ...

Implementing Delete Join in Client Applications

When executing a DELETE...JOIN from an application, use prepared statements to safely pass values like status or IDs.

Python (using mysql-connector-python)

import mysql.connector
from mysql.connector import Error

def delete_inactive_customers(status_to_delete):
    # Note: Deleting from both tables here.
    query = """DELETE c, o
               FROM CUSTOMERS AS c
               INNER JOIN ORDERS AS o ON c.ID = o.CUSTOMER_ID
               WHERE c.STATUS = %s"""

    try:
        with mysql.connector.connect(/*...db config...*/, database='TUTORIALS') as conn:
            with conn.cursor() as cursor:
                cursor.execute(query, (status_to_delete,))
                conn.commit() # Important: Commit the transaction
                print(f"{cursor.rowcount} rows were deleted.")

    except Error as e:
        print(f"Error: {e}")

# Call the function to delete all 'inactive' customers and their orders
delete_inactive_customers('inactive')