DELETE Join in PostgreSQL

PostgreSQL does not have a DELETE JOIN statement like MySQL. Instead, it provides the USING clause, which lets you reference additional tables to control which rows are deleted from the target table. The join condition is expressed in WHERE, not with an explicit JOIN keyword. For cross-database portability, a subquery using WHERE col IN (SELECT ...) achieves the same result.

Syntax

DELETE FROM target_table
USING joined_table
WHERE target_table.join_col = joined_table.join_col;

Multiple joined tables:

DELETE FROM target_table
USING table2, table3
WHERE target_table.id = table2.ref_id
  AND table2.category_id = table3.id;

Practical Example

Create a subscribers table and a suppression_list table, then remove suppressed subscribers:

CREATE TABLE subscribers (
  id      SERIAL PRIMARY KEY,
  email   VARCHAR(255) NOT NULL UNIQUE,
  list    VARCHAR(50)  NOT NULL,
  active  BOOLEAN NOT NULL DEFAULT true
);

CREATE TABLE suppression_list (
  email   VARCHAR(255) NOT NULL,
  reason  VARCHAR(50)  NOT NULL,
  added_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

INSERT INTO subscribers (email, list)
VALUES
  ('alex.moore@example',   'newsletter'),
  ('jamie.wu@example',     'product'),
  ('riley.santos@example', 'newsletter'),
  ('drew.clark@example',   'product');

INSERT INTO suppression_list (email, reason)
VALUES
  ('jamie.wu@example',  'hard_bounce'),
  ('drew.clark@example', 'unsubscribed');

Delete all subscribers whose email appears in the suppression list:

DELETE FROM subscribers
USING suppression_list
WHERE subscribers.email = suppression_list.email;

Output:

DELETE 2

Verify the remaining subscribers:

SELECT id, email, list FROM subscribers ORDER BY id;

 id |           email            |    list
----+----------------------------+-------------
  1 | alex.moore@example    | newsletter
  3 | riley.santos@example  | newsletter
(2 rows)

Use RETURNING to log the deleted email addresses:

DELETE FROM subscribers
USING suppression_list
WHERE subscribers.email = suppression_list.email
RETURNING subscribers.id, subscribers.email, suppression_list.reason;

Subquery Alternative

The USING clause is PostgreSQL-specific. For SQL that must run on multiple database systems, use a subquery:

DELETE FROM subscribers
WHERE email IN (
  SELECT email
  FROM suppression_list
);

Both approaches produce identical results. The USING syntax often produces a better query plan in PostgreSQL; the subquery form is preferred when portability matters.

USING vs Subquery Comparison

Testing with Vela

Delete-join operations are powerful and irreversible. Use Vela database branching to run the DELETE USING against a branch of your production data before applying it to the live database. Inspect the RETURNING output on the branch, confirm the affected row count, and verify that no unintended rows were targeted — all without any risk to production data.

Production Tips

• Test the join condition with a SELECT first to confirm it targets exactly the rows you intend to remove.
• USING performs an implicit inner join — rows in the target table with no match in the joined table are not deleted.
• Use RETURNING to capture deleted rows for audit logs or downstream processing.
• Add a foreign key index on the join columns in both tables to avoid full table scans on large datasets.
• Wrap the DELETE in a transaction so you can ROLLBACK if the result is unexpected.
• For very large deletion batches, consider chunking with LIMIT inside a CTE to release locks between commits.