DELETE ... USING in SQL: JOIN-Style Deletes Without a Subquery

DELETE ... USING lets you delete rows from one table while filtering them against another, join-style, with no correlated subquery. It is native PostgreSQL syntax, and several other engines offer the same idea with small twists.

Basic syntax

Say you need to delete every order placed by users in the US. The country lives in the users table, but you want to delete from orders. List the filtering table in USING and put the link condition in WHERE.

DELETE FROM orders o
USING users u
WHERE o.user_id = u.id
  AND u.country = 'US';

Here orders is the target table, and rows leave it. users is brought in only for filtering and is never modified itself. The o.user_id = u.id clause plays the role of ON in an ordinary JOIN, and u.country = 'US' is an extra filter. Exactly the orders rows that find a matching partner in users get deleted.

• USING holds the table you filter against, not the one you delete from.
• The link between tables is a plain predicate in WHERE, not a separate ON.
• Aliases (o, u) keep the query readable and are required when column names collide.

Multiple tables in USING

You can list several tables in USING, comma-separated; they join to each other and to the target through the conditions in WHERE. Let's delete orders tied to managers in the sales department.

DELETE FROM orders o
USING users u, employees e
WHERE o.user_id = u.id
  AND u.id = e.manager_id
  AND e.dept = 'sales';

The logic mirrors a three-table JOIN: PostgreSQL builds the join orders x users x employees, then deletes every orders row that survives. If one orders row matches several rows on the right, it is still deleted just once; duplicates on the right do not multiply the delete.

DELETE ... USING vs a WHERE IN subquery

The classic way to express the same thing is a subquery:

DELETE FROM orders
WHERE user_id IN (
  SELECT id FROM users WHERE country = 'US'
);

Both are correct, but there is a practical difference:

• USING exposes the second table's columns right inside WHERE, so filtering on several fields stays flat, with no nesting.
• An IN subquery returns a single column; to match a pair of columns you must switch to EXISTS or row constructors, and readability suffers.
• The PostgreSQL planner usually turns both forms into a semi-/hash-join, so they perform similarly. But USING is clearer when you also need a condition from the second table, not just a list of keys.

Gotcha: NOT IN with a subquery silently breaks on NULL — if the subquery yields even one NULL, it returns empty and deletes nothing. For "delete orders of users not in a whitelist", prefer NOT EXISTS or LEFT JOIN ... IS NULL over NOT IN.

WHERE safety: the main risk

The costliest mistake with DELETE ... USING is dropping or mangling the link condition. Remove o.user_id = u.id and you get a Cartesian product, which deletes the whole target table: every orders row finds at least one partner in users.

-- DANGER: no join predicate -> deletes every row in orders
DELETE FROM orders o
USING users u
WHERE u.country = 'US';

Simple safeguards:

• First turn the DELETE into a SELECT with the same FROM/USING/WHERE and inspect what would be removed.
• Wrap the operation in a transaction (BEGIN; ... ROLLBACK;) until you trust the row count.
• Check the reported row count: if it looks suspiciously large, do not COMMIT.

Multi-table DELETE in MySQL

MySQL has no USING in this sense; instead it offers a multi-table DELETE with an explicit JOIN. The key detail: between DELETE and FROM you list which tables you actually delete rows from.

DELETE o
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.country = 'US';

Write DELETE o, u and you remove rows from both tables at once — sometimes handy, but riskier. In ClickHouse deletes are asynchronous (ALTER TABLE ... DELETE, or a lightweight DELETE in newer versions) and there is no USING join semantics at all. Bottom line: in PostgreSQL reach for DELETE ... USING, in MySQL for DELETE ... JOIN, and always verify the link predicate before you run it.