Introduction

Sometimes you need to delete rows from one table only when matching rows exist in another table. That pattern is often described as DELETE with INNER JOIN, but the exact syntax depends on the database engine you are using.

What the Join Is Doing

The join does not delete from both tables. It identifies which rows in the target table should be removed by matching them against another table.

Imagine these tables:

• 'orders'
• 'customers'

If you want to delete orders for inactive customers, the join condition finds orders whose customer_id matches a customer row with active = 0.

MySQL Style Syntax

In MySQL, you can name the table being deleted right after DELETE and then join to the related table.

1DELETE o
2FROM orders AS o
3INNER JOIN customers AS c
4    ON o.customer_id = c.id
5WHERE c.active = 0;

This removes rows from orders only. The customers table is used for filtering, not for deletion.

Before running the delete, it is smart to preview the affected rows with a SELECT using the same join and WHERE clause:

1SELECT o.*
2FROM orders AS o
3INNER JOIN customers AS c
4    ON o.customer_id = c.id
5WHERE c.active = 0;

If the preview is wrong, the delete will be wrong too.

SQL Server and PostgreSQL Variants

SQL Server uses a similar pattern:

1DELETE o
2FROM orders AS o
3INNER JOIN customers AS c
4    ON o.customer_id = c.id
5WHERE c.active = 0;

PostgreSQL does not use DELETE ... JOIN in the same way. Instead, it uses USING:

1DELETE FROM orders AS o
2USING customers AS c
3WHERE o.customer_id = c.id
4  AND c.active = false;

That difference is easy to forget when moving between databases.

Use Transactions for Safety

Deletes are destructive, so wrap them in a transaction when you are cleaning important data:

1BEGIN;
2
3DELETE o
4FROM orders AS o
5INNER JOIN customers AS c
6    ON o.customer_id = c.id
7WHERE c.active = 0;
8
9-- Inspect row counts before committing
10COMMIT;

In development or maintenance work, you can replace COMMIT with ROLLBACK while testing the query.

Consider Constraints and Cascades

A join-based delete does not bypass foreign keys. If other tables still reference the rows you are trying to remove, the delete may fail unless cascading rules are set up.

That is why production cleanup scripts often include:

• a preview SELECT
• an explicit transaction
• a row count check
• an understanding of foreign-key behavior

The join narrows the target set, but the database still enforces normal integrity rules.

Common Pitfalls

The most common mistake is forgetting which table is the actual delete target. In MySQL and SQL Server, only the table named after DELETE is removed from.

Another issue is testing the join logic with a broad DELETE before previewing it with SELECT. Always verify the exact rows first.

It is also easy to paste MySQL syntax into PostgreSQL and get a syntax error. The underlying idea is the same, but the statements are not portable word for word.

Summary

• 'DELETE with a join removes rows from one target table based on matching rows in another table.'
• MySQL and SQL Server support DELETE alias FROM ... JOIN ....
• PostgreSQL uses DELETE ... USING ... for the same general pattern.
• Run the equivalent SELECT first so you can verify the affected rows.
• Use transactions and check foreign-key constraints before running destructive deletes.