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

Use SQL to delete rows with no matches

When working with multiple tables in a relational database, it is sometimes necessary to identify and delete rows in one table that do not have corresponding matches in another table. This process is often called "removing orphan entries".

Consider the following scenario:

• You have two tables, "files" and "blob", where "files.id" can be used to join with "blob.fileid".
• Your goal is to delete all rows in the "blob" table that do not have a matching "id" value in the "files" table.

To do this, you can use various SQL methods:

Use LEFT JOIN and IS NULL

<code class="language-sql">DELETE b FROM BLOB b
LEFT JOIN FILES f ON f.id = b.fileid
WHERE f.id IS NULL</code>

Use EXISTS subquery

<code class="language-sql">DELETE FROM BLOB
WHERE NOT EXISTS(SELECT NULL
                    FROM FILES f
                   WHERE f.id = fileid)</code>

Use NOT IN subquery

<code class="language-sql">DELETE FROM BLOB
WHERE fileid NOT IN (SELECT f.id
                        FROM FILES f)</code>

NOTE: If possible, it is recommended to perform delete operations within a transaction so that you have the option to roll back the changes if any unexpected issues arise.

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