How to Efficiently Delete Rows from One SQL Table Based on IDs in Another?

Deleting Rows Based on Another Table

In SQL, it's not uncommon to encounter the need to delete rows from a table based on their presence in another table. Consider the following scenario:

DB Query:

I can't seem to ever remember this query!

DB Query Goal:

I want to delete all rows in Table1 whose IDs are the same as in Table2. This can be expressed as:

DELETE table1 t1
WHERE t1.ID = t2.ID

While the subquery approach is valid, we'll explore how to achieve this using a JOIN for improved performance:

DB Query Solution using JOIN:

DELETE t1 
FROM Table1 t1
JOIN Table2 t2 ON t1.ID = t2.ID;

By leveraging the JOIN, we establish a relationship between the rows in Table1 and Table2 based on their ID column. Rows in Table1 that share ID values with rows in Table2 will be identified and eligible for deletion.

Note:

• Using aliases in the DELETE statement is recommended.
• Prevent accidental deletions by highlighting the entire query before running it to avoid deleting the entire table instead of specific rows.

The above is the detailed content of How to Efficiently Delete Rows from One SQL Table Based on IDs in Another?. For more information, please follow other related articles on the PHP Chinese website!