How to Delete Orphan Rows in SQL Using LEFT JOIN, NOT EXISTS, or NOT IN?

Delete the row of no matching in the SQL table

Delete the orphan entry in the SQL table. One of the common situation is that in the identification table, there is a lack of corresponding matching rows in another table. Consider the following:

You have two tables:

Files:

Contains a ID column.
• Table BLOB: Contains a FileID column.
• FileID and ID columns can be used for connection tables. Your goal is to delete all lines that cannot be found in the table.id in the table Blob.
For this task, there are three main methods:

Use left join/is null:

Use not exists:

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

Use not in:

Warning:

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

When performing a delete operation, make sure they execute them in affairs. This allows you to roll back to change when any error occurs, thereby minimizing the risk of data loss.

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