MySQL Delete Join: Delete Rows Based on Another Table

Often you need to delete rows depending on another table: “delete the orders of inactive customers”, “delete the orphans that have no parent”.

query.sql

SQL

CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(20), active BOOLEAN NOT NULL);
CREATE TABLE orders (id INT AUTO_INCREMENT PRIMARY KEY, customer_id INT NOT NULL, total DECIMAL(8,2) NOT NULL);
INSERT INTO customers VALUES (1, 'Maya', TRUE), (2, 'Rohan', FALSE), (3, 'Sana', TRUE);
INSERT INTO orders (customer_id, total) VALUES (1, 100), (2, 200), (2, 50), (3, 75), (9, 10);

Order 5 belongs to customer 9, who does not exist: an orphan.

DELETE with JOIN

Name the table you want to delete from between DELETE and FROM:

query.sql

SQL

DELETE o
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.active = FALSE;

SELECT id, customer_id, total FROM orders ORDER BY id;

Output

OUTPUT

+----+-------------+--------+
| id | customer_id | total  |
+----+-------------+--------+
|  1 |           1 | 100.00 |
|  4 |           3 |  75.00 |
|  5 |           9 |  10.00 |
+----+-------------+--------+
3 rows in set

Only orders rows were deleted (DELETE o). The customer rows stay. You could delete from both by naming both: DELETE o, c FROM ....

Deleting orphans with LEFT JOIN

Rows whose parent does not exist:

query.sql

SQL

DELETE o
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

SELECT id, customer_id, total FROM orders ORDER BY id;

Output

OUTPUT

+----+-------------+--------+
| id | customer_id | total  |
+----+-------------+--------+
|  1 |           1 | 100.00 |
|  4 |           3 |  75.00 |
+----+-------------+--------+
2 rows in set

The LEFT JOIN keeps orders with no customer, and those have c.id IS NULL.

The same with NOT EXISTS (standard SQL)

query.sql

SQL

INSERT INTO orders (customer_id, total) VALUES (8, 5);

DELETE FROM orders
WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.id = orders.customer_id);

SELECT id, customer_id, total FROM orders ORDER BY id;

Output

OUTPUT

+----+-------------+--------+
| id | customer_id | total  |
+----+-------------+--------+
|  1 |           1 | 100.00 |
|  4 |           3 |  75.00 |
+----+-------------+--------+
2 rows in set

NOT EXISTS is portable, and handles NULL safely (EXISTS).

Deleting duplicates

A classic job: keep one copy of each duplicated row. With a primary key and MySQL 8, a window function makes it clean:

query.sql

SQL

CREATE TABLE subscribers (id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(40) NOT NULL);
INSERT INTO subscribers (email) VALUES ('a@example.com'), ('b@example.com'), ('a@example.com'), ('c@example.com'), ('b@example.com'), ('a@example.com');

DELETE FROM subscribers
WHERE id IN (
    SELECT id FROM (
        SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS copy_number
        FROM subscribers
    ) AS numbered
    WHERE copy_number > 1
);
SELECT id, email FROM subscribers ORDER BY id;

Output

OUTPUT

+----+---------------+
| id | email         |
+----+---------------+
|  1 | a@example.com |
|  2 | b@example.com |
|  4 | c@example.com |
+----+---------------+
3 rows in set

Each email keeps its lowest id (copy number 1). The subquery sits inside a derived table, to avoid error 1093 (“You can’t specify target table for update in FROM clause”). The same job with a self-join:

query.sql

SQL

INSERT INTO subscribers (email) VALUES ('a@example.com'), ('c@example.com');

DELETE s1 FROM subscribers s1
JOIN subscribers s2 ON s1.email = s2.email AND s1.id > s2.id;
SELECT id, email FROM subscribers ORDER BY id;

Output

OUTPUT

+----+---------------+
| id | email         |
+----+---------------+
|  1 | a@example.com |
|  2 | b@example.com |
|  4 | c@example.com |
+----+---------------+
3 rows in set

After removing duplicates, prevent them with a unique index: ALTER TABLE subscribers ADD UNIQUE KEY uq_email (email); (UNIQUE).

Rules

ORDER BY and LIMIT are not allowed in a multi-table DELETE.
Preview with a SELECT using the same FROM, JOIN and WHERE (replace DELETE o by SELECT o.*).
Index the join columns.
Do big deletes in batches (Changing Millions of Rows).

lightbulb

Fix the cause

Orphans appear when there is no foreign key. After cleaning them, add a foreign key so the database refuses to create new ones.