How to Use DELETE JOIN in PostgreSQL (With Example)

Often in PostgreSQL you may want to delete rows in one table based on joining matching rows in another table.

You can use the USING statement with the following syntax to do so:

DELETE FROM store1
USING store2
WHERE store1.product = store2.product;

This particular example will delete all rows from the store1 table that have a matching value in the product column of the store2 table.

The following example shows how to use this syntax in practice.

Example: How to Use DELETE JOIN in PostgreSQL

Suppose that we create the following table named store1 that contains information about products sold at one retail store:

-- create table named 'store1'
CREATE TABLE store1 (
  product TEXT,
  sales INTEGER
);

-- add rows to the table 
INSERT INTO store1 VALUES ('A', 22);
INSERT INTO store1 VALUES ('B', 16);
INSERT INTO store1 VALUES ('C', 40);
INSERT INTO store1 VALUES ('D', 31);
INSERT INTO store1 VALUES ('E', 12);

-- view all rows in table
SELECT * FROM store1;

 product | sales 
---------+-------
 A       |    22
 B       |    16
 C       |    40
 D       |    31
 E       |    12

Then suppose we create another table named store2 that contains information about products sold at another retail store:

-- create table named 'store2'
CREATE TABLE store2 (
  product TEXT,
  sales INTEGER
);

-- add rows to the table 
INSERT INTO store2 VALUES ('A', 22);
INSERT INTO store2 VALUES ('B', 16);
INSERT INTO store2 VALUES ('C', 40);
INSERT INTO store2 VALUES ('F', 31);
INSERT INTO store2 VALUES ('G', 12);

-- view all rows in table
SELECT * FROM store2;

 product | sales 
---------+-------
 A       |    22
 B       |    16
 C       |    23
 F       |    31
 G       |    12

We can use the following syntax to delete all rows from the store1 table that have a matching value in the product column of the store2 table:

-- delete rows from store1 with matching values in product column of store2 
DELETE FROM store1
USING store2
WHERE store1.product = store2.product;

-- view updated store1 table
SELECT * FROM store1;

 product | sales 
---------+-------
 D       |    31
 E       |    12

When we view the updated store1 table we can see that all rows with a matching value in the product column in the store2 table have been deleted from the store1 table.

You can use similar syntax to perform a DELETE JOIN operation in your own table in PostgreSQL.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Use INNER JOIN with GROUP BY
PostgreSQL: How to Use LEFT JOIN on Multiple Tables
PostgreSQL: How to Perform an Anti-Join