How to Delete Orphan Rows in MySQL Using JOINs, NOT EXISTS, or NOT IN?

Efficiently Removing Orphan Records in MySQL: Three Proven Methods

This guide demonstrates three effective techniques for deleting orphan rows in MySQL—database entries lacking corresponding records in a related table based on a shared ID.

Method 1: Leveraging LEFT JOIN and IS NULL

This approach utilizes a LEFT JOIN to combine data from two tables based on a specific ID column. Orphan rows, identified by a NULL value in the joined table's ID column, are then targeted for deletion.

DELETE b FROM BLOB b
LEFT JOIN FILES f ON f.id = b.fileid
WHERE f.id IS NULL;

Method 2: Employing NOT EXISTS

This method employs a subquery with NOT EXISTS to check for the presence of matching records in the related table. Rows lacking a match are flagged for deletion.

DELETE FROM BLOB
WHERE NOT EXISTS (SELECT NULL
FROM FILES f
WHERE f.id = fileid);

Method 3: Utilizing NOT IN

This straightforward approach uses NOT IN to compare IDs directly. Rows with IDs absent from the related table are identified and deleted.

DELETE FROM BLOB
WHERE fileid NOT IN (SELECT f.id
FROM FILES f);

Important Consideration:

Always execute DELETE statements within a transaction to ensure data integrity. This allows for rollback in case of errors or unintended consequences. Use START TRANSACTION before the DELETE and COMMIT (or ROLLBACK) afterward.

The above is the detailed content of How to Delete Orphan Rows in MySQL Using JOINs, NOT EXISTS, or NOT IN?. For more information, please follow other related articles on the PHP Chinese website!