Right, so you’ve mastered deleting rows from a single table. That’s cute. Welcome to the big leagues, where DELETE statements get a power-up and the potential for catastrophic error multiplies accordingly. The USING clause is how you tell PostgreSQL, “I need to delete from this table over here, but to figure out which rows, I first need to poke around in these other tables over there.” It’s your join condition for a deletion.

Think of it like this: the table after DELETE FROM is the victim. The table(s) in the USING clause are the accomplices. You’re using the accomplices to identify the victim, and then you’re… well, deleting the victim.

The Anatomy of a USING Clause

The syntax looks deceptively simple, which is why it trips so many people up.

DELETE FROM target_table
USING other_table
WHERE target_table.foreign_key_id = other_table.id
AND other_table.some_criterion = true;

Here’s the critical part that everyone misses at first: the USING clause essentially creates an implicit inner join between your target table and the other table(s) you list. Your WHERE clause then filters the results of that join. Only the rows from the target table that survive this join-and-filter process get deleted.

Let’s get concrete. Imagine you’re running a dating app for cats, and you need to remove all profiles for cats whose owners have deleted their accounts.

-- Our 'accomplice' table: identifies which owner IDs are gone
DELETE FROM cat_profiles
-- Our 'victim' table: the rows we want to remove
USING owners
-- The crucial link: we find cat_profiles that reference a deleted owner
WHERE cat_profiles.owner_id = owners.id
-- The specific reason for the deletion: the owner is gone
AND owners.deleted_at IS NOT NULL;

In this query, cat_profiles is the target. We USE the owners table to find out which owners are deleted. The WHERE clause connects the two (cat_profiles.owner_id = owners.id) and applies the final criterion (owners.deleted_at IS NOT NULL). The result: every cat_profiles row linked to a deleted owner gets nuked.

Why Not Use a Subquery?

You might be thinking, “Couldn’t I just use a subquery in the WHERE clause?” You absolutely could. The previous query is functionally identical to:

DELETE FROM cat_profiles
WHERE owner_id IN (
    SELECT id FROM owners WHERE deleted_at IS NOT NULL
);

So why bother with USING? Two reasons:

  1. Performance: For complex multi-table joins, the planner can often optimize a USING clause better than a slew of subqueries.
  2. Clarity (and necessity) for Multi-Table Joins: The USING clause truly shines when your deletion logic depends on a join between multiple other tables. A subquery would get messy fast.

The Real Power: Multiple Accomplice Tables

This is where the USING clause becomes indispensable. Let’s say you want to delete all messages sent from cats who belong to owners who live in a specific city. Now we need to join through two accomplice tables.

DELETE FROM messages
USING cat_profiles, owners
WHERE messages.sender_cat_id = cat_profiles.id
AND cat_profiles.owner_id = owners.id
AND owners.city = 'Slough'; -- A fitting fate for any message

See how that works? We’re using both cat_profiles and owners to identify which messages rows to delete. The WHERE clause defines the entire chain of relationships. Trying to write this with subqueries would be a nested nightmare.

The Pitfall of Ambiguity

Here’s the classic rookie mistake. What if both your target table and a USING table have a column with the same name, like id?

-- DANGER! WILL ROBINSON!
DELETE FROM orders
USING customers
WHERE id = customers.id; -- Ambiguous! Which 'id' is which?

This query is a disaster waiting to happen. The parser doesn’t know if you mean orders.id or customers.id. In some cases, it might guess wrong. In others, it’ll just error out. Always, always qualify your column names in the WHERE clause. Be explicit.

-- The safe, correct way
DELETE FROM orders
USING customers
WHERE orders.customer_id = customers.id -- Much better
AND customers.status = 'banned';

The RETURNING Clause: Your Safety Net

I never, ever run a significant DELETE without a RETURNING * clause first. It’s the single best practice you can adopt. Before you execute the actual deletion, run a SELECT ... USING to see what would be deleted.

-- First, do a dry run. ALWAYS.
SELECT orders.*
FROM orders
USING customers
WHERE orders.customer_id = customers.id
AND customers.status = 'banned';

-- Look at the results. Are these indeed the rows you want to vanish forever?
-- If yes, only then change SELECT orders.* to DELETE FROM orders and add RETURNING *.

-- The final, verified command
DELETE FROM orders
USING customers
WHERE orders.customer_id = customers.id
AND customers.status = 'banned'
RETURNING orders.id, orders.total; -- This shows you what you just deleted

The RETURNING clause is your confirmation slip. It tells you exactly what was removed. It turns a terrifying leap of faith into a verifiable operation. Use it. Your future self, who isn’t frantically restoring a database from backup, will thank you.